x

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

more ▼

asked Oct 26, 2011 at 12:13 PM in Default

avatar image

dmeenan
28 2 2 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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;
more ▼

answered Feb 02, 2012 at 07:46 PM

avatar image

SQL Kiwi
1.2k 1 3 6

Thanks for the clarification Paul!

Feb 02, 2012 at 10:56 PM grrl_geek
(comments are locked)
10|1200 characters needed characters left
  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?

more ▼

answered Feb 02, 2012 at 02:36 PM

avatar image

grrl_geek
866 1 3 7

(comments are locked)
10|1200 characters needed characters left
  1. You need to check version of SQL Server for filegroup restore. It is supported in SQL Server Enterprise Edition only.

more ▼

answered Feb 02, 2012 at 06:24 PM

avatar image

inder
211 18 19 25

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x449
x248
x134
x44

asked: Oct 26, 2011 at 12:13 PM

Seen: 3206 times

Last Updated: Feb 02, 2012 at 10:56 PM

Copyright 2018 Redgate Software. Privacy Policy