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:
One particularly large “audit” table in its own filegroup/file.
I want to be able to truncate this table once a quarter after first...
Taking a FileGroup backup and saving it to cheap, tier3 disk.
Then, in the unlikely situation where an auditor needs to see this table, from say 2011Q4,…
I do a redirected restore of the FileGroup backup to a new, throwaway database.
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.
if exists (select from sysdatabases where name = 'FGTest1') drop database FGTest1
if exists (select from sysdatabases where name = 'FGTest2') drop database FGTest2
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)
-- 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)
---- Create Table
CREATE TABLE dbo.MsgResults(
MsgID int constraint PK_MsgResults_MsgID primary key not null,
ProcessDtTm datetime NOT NULL ) on FileGroup1
insert into dbo.MsgResults (MsgID, [Name], ProcessDttm)
select id, [name], crDate from master.dbo.sysobjects
backup database FGTest1 FileGroup = 'FileGroup1'
to disk = 'C:...\FGTest1_FileGroup1.bak' with COPY_ONLY, COMPRESSION, INIT
RESTORE FILELISTONLY FROM DISK = 'C:...\FGTest1_FileGroup1.bak'
alter database FGTest2 set single_user with rollback immediate
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'
alter database FGTest2 set multi_user
Oct 26, 2011 at 12:13 PM