asked
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
1. Are you using Enterprise edition? 2. In your restore, it does not appear that you are restoring the PRIMARY filegroup also. Have you tried that?
1. You need to check version of SQL Server for filegroup restore. It is supported in SQL Server Enterprise Edition only.
Nonsense. See
http://msdn.microsoft.com/en-us/library/ms190710.aspx
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;
3 People are following this question.
