partial backup and restore help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

partial backup and restore help

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-09-23 : 15:07:56

I have a 180GB database called MyDB. It has 3 files: primary (system data), other (most user data), and big (blobs).

What scripts do I use to backup the primary and other files and then restore them to another server. I don't want to backup and restore the big file due to space constraints.

I have tried several attempts with EM and QA, but I have been unsuccessful. Everytime it seems like a differnt everytime.

Thanks,
Ray
SS2k

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-09-27 : 13:11:03

Okay here are my scripts. Let me know if anyone has any ideas why I am getting the errors during the partial restore. Thanks. - ray

-- purpose: partial backup from ServerA, backup 2 of the 3 files (not the blobs)
-- results: successful

BACKUP database multiplefiles
FILE = 'multiplefiles_data',
FILEGROUP = 'Primary',
FILE = 'other_data',
FILEGROUP = 'other'
TO disk = '\\FileServer\MultipleFiles.dat'

BACKUP log multiplefiles
TO disk = '\\iis-file\Shared\IT Dept\Staff\RBouknight\MultipleFiles.dat'

-- purpose: partial restore to ServerB, restore 2 of the 3 files (not the blobs)
-- results: see error below

RESTORE DATABASE test_part
FILEGROUP = 'primary'
FROM DISK='\\FileServer\MultipleFiles.dat'
WITH FILE=1, NORECOVERY,PARTIAL,
MOVE 'MultipleFiles_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test.pri',
MOVE 'MultipleFiles_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test.log'
GO
RESTORE DATABASE test_part
FILEGROUP = 'other'
FROM DISK='\\FileServer\MultipleFiles.dat'
WITH FILE=1, NORECOVERY,PARTIAL,
MOVE 'Other_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\test.dat2'
GO

RESTORE LOG test_part
FROM DISK = '\\FileServer\MultipleFiles.dat'
WITH FILE = 1, RECOVERY
GO

-- error results from partial restore above

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file '\\FileServer\MultipleFiles.dat' was created by BACKUP DATABASE...FILE=<name> and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file '\\FileServer\MultipleFiles.dat' was created by BACKUP DATABASE...FILE=<name> and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 913, Level 16, State 8, Line 2
Could not find database ID 65535. Database may not be activated yet or may be in transition.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-09-27 : 15:12:19

OK - I think I found the problem. I cannot do a partial restore from a partial/file/filegroup backup. Partial restores can only be done from a FULL backup. See BOL subject "Partial Database Restore Operations", in particular the line that reads "Partial database restore of file backups is not supported."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources