Can you do a SQL Server 2008 Filegroup backup redirected restore?
Problem – I want to be able to do a FileGroup-restore to a new database. Situation – I have a very large db in which there is: 1. One particularly large “audit” table in its own filegroup/file. 2. I want to be able to truncate this table once a quarter after first... 3. Taking a FileGroup backup and saving it to cheap, tier3 disk. 4. Then, in the unlikely situation where an auditor needs to see this table, from say 2011Q4,… 5. I do a redirected restore of the FileGroup backup to a new, throwaway database. 6. Looks like this isn't possible according to
http://support.microsoft.com/kb/281122 search for "cannot" in the article .
Hoping someone has a workaround?! Tried this script below – doesn’t work
(replaced full paths with ellipses for brevity’s sake). I Get this error: Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'FGTest2' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. -- -- -- use master go if exists (select * from sysdatabases where name = 'FGTest1') drop database FGTest1 if exists (select * from sysdatabases where name = 'FGTest2') drop database FGTest2 go CREATE DATABASE [FGTest1] ON PRIMARY (NAME = N'FGTest', FILENAME = N'C:\...\FGTest1.mdf', SIZE = 1280KB), FILEGROUP [FileGroup1] (NAME = N'FGTest_Index', FILENAME = N'C:\...\FGTest1_Index.ndf', SIZE = 2048KB) LOG ON (NAME = N'FGTest_log', FILENAME = N'C:\...\FGTest1_log.LDF', SIZE = 1024KB) Go -- Create the throwaway database with identical group and logical file names. CREATE DATABASE [FGTest2] ON PRIMARY (NAME = N'FGTest', FILENAME = N'C:\...\FGTest2.mdf', SIZE = 1280KB), FILEGROUP [FileGroup1] (NAME = N'FGTest_Index', FILENAME = N'C:\...\FGTest2_Index.ndf', SIZE = 2048KB) LOG ON (NAME = N'FGTest_log', FILENAME = N'C:\...\FGTest2_log.LDF', SIZE = 1024KB) GO use FGTest1 go ---- Create Table CREATE TABLE dbo.MsgResults( MsgID int constraint PK_MsgResults_MsgID primary key not null, [Name] varchar(100), ProcessDtTm datetime NOT NULL ) on FileGroup1 go insert into dbo.MsgResults (MsgID, [Name], ProcessDttm) select id, [name], crDate from master.dbo.sysobjects go use master go backup database FGTest1 FileGroup = 'FileGroup1' to disk = 'C:\...\FGTest1_FileGroup1.bak' with COPY_ONLY, COMPRESSION, INIT go RESTORE FILELISTONLY FROM DISK = 'C:\...\FGTest1_FileGroup1.bak' go alter database FGTest2 set single_user with rollback immediate go RESTORE DATABASE FGTest2 Filegroup = 'FileGroup1' FROM DISK = 'C:\...\FGTest1_FileGroup1.bak' WITH recovery, replace, stats=1, MOVE 'FGTest1' TO 'C:\...\FGTest2.mdf', MOVE 'FG_Index' TO 'C:\...\FGTest2_Index.ndf', MOVE 'FGTest1_log' TO 'C:\...\FGTest2_log.ldf' go alter database FGTest2 set multi_user
You **don't** need Enterprise Edition to make this work, but you should have a minimally-sized `PRIMARY` file group to make the process as fast and efficient as possible (this is a best practice anyway). The following script is well commented, and outlines the process you could follow. It requires that C:\Test exists and is writeable by SQL Server; change the path if necessary: USE master; GO -- This simulates your production database -- PRIMARY filegroup is empty -- DEFAULT filegroup is called Main -- Table to archive is on filegroup Big CREATE DATABASE Test ON PRIMARY (NAME = Test, FILENAME = 'C:\Temp\Test.mdf'), FILEGROUP Main DEFAULT (NAME = Main, FILENAME = 'C:\Temp\Main.ndf'), FILEGROUP Big (NAME = Big, FILENAME = 'C:\Temp\Big.ndf') LOG ON (NAME = Test_Log, FILENAME = 'C:\Temp\Test.ldf'); GO USE Test; GO -- Initial backup BACKUP DATABASE Test TO DISK = 'C:\Temp\FullBackup.bak' WITH FORMAT, CHECKSUM; GO -- Create tables CREATE TABLE dbo.Ordinary (col1 integer NULL); CREATE TABLE dbo.Big (col1 integer NULL) ON Big; GO -- Time passes, data gets added INSERT dbo.Ordinary VALUES (12345); INSERT dbo.Big VALUES (9876), (5432), (10); GO -- End of quarter, archive Big table -- Set FG to READ_ONLY first ALTER DATABASE Test MODIFY FILEGROUP Big READ_ONLY; -- Backup only Big and empty PRIMARY BACKUP DATABASE Test FILEGROUP = 'PRIMARY', FILEGROUP = 'Big' TO DISK = 'C:\Temp\Big.bak' WITH FORMAT, CHECKSUM; -- Reset to read/write ALTER DATABASE Test MODIFY FILEGROUP Big READ_WRITE; -- Clear the table TRUNCATE TABLE dbo.Big; -- Add new data INSERT dbo.Big VALUES (9999); GO -- Later, we need to recover the saved data -- Recover to a new database name RESTORE DATABASE Recovered FILEGROUP = 'PRIMARY', FILEGROUP = 'Big' FROM DISK = 'C:\Temp\Big.bak' WITH RECOVERY, MOVE 'Test' TO 'C:\Temp\TestRecovered.mdf', MOVE 'Big' TO 'C:\Temp\BigRecovered.ndf', MOVE 'Test_Log' TO 'C:\Temp\TestRecovered.ldf'; GO USE Recovered; GO -- Note 'main' is RECOVERY_PENDING -- We only restored empty PRIMARY and the Big FG SELECT * FROM sys.database_files AS df; GO -- Big table is online and accessible SELECT * FROM dbo.Big AS b; GO -- Error 8653: -- The query processor is unable to produce a plan for -- the table or view 'Ordinary' because the table -- resides in a filegroup which is not online. SELECT * FROM dbo.Ordinary AS o; GO -- Tidy up USE master DROP DATABASE Test; DROP DATABASE Recovered;