May 20, 2004 at 8:39 am
I have setup a backup plan that performs a full backup on the weekends with a differential backup each night during the week.
I was just trying to restore one of the databases to a new database for testing. I am using the following T-SQL to do the restore:
RESTORE DATABASE TESTDB
FROM DISK = 'C:\Backups\PRODDB.BAK'
WITH MOVE 'PRODDB_Data' TO 'C:\Data\TESTDB_Data.MDF',
MOVE 'PRODDB_Log' TO 'C:\Data\TESTDB_Log.LDF',
NORECOVERY
RESTORE DATABASE TESTDB
FROM DISK = ''C:\Backups\PRODDB_DIFF.BAK'
WITH MOVE 'PRODDB_Data' TO 'C:\Data\TESTDB_Data.MDF',
MOVE 'PRODDB_Log' TO 'C:\Data\TESTDB_Log.LDF',
RECOVERY
The restore of the full backup works fine but when I run the restore on the differential backup I get the following error:
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'C:\Backups\PRODDB_DIFF.BAK' to database 'TESTDB'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What am I doing wrong?
Thanks.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
May 20, 2004 at 10:12 am
Got to ask an obvious question....was the differential you are restoring made AFTER the full backup that you restored?
-SQLBill
May 20, 2004 at 10:22 am
Yes the full backup was made before the differential but as I am writing this answer I think I know what the problem is. My "production" backup plan runs a full database backup every night at 7:00 PM. The differential backup plan has been setup as a test plan. This plan runs a full backup on Sunday night then a differential backup each week night.
The differential backup plan made a backup last Sunday night and the differential backup was from last night (Wednesday) at 6:45 PM.
The differential backup was only backing up changes from the previous full backup, which was on Tuesday night (my "production" backup plan). Since the differential backup didn't contain all changes from the full backup I first restored (Sunday night's), I bet that is what gave me the error.
I will test this idea and post my results later.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
May 20, 2004 at 11:06 pm
you can not use the MOVE options with diff. backup restore.
try by removing MOVE files options from diff. restore command.
May 27, 2004 at 3:26 pm
I finally got around to testing my theory and I believe I was correct.
You can use the MOVE option with a differential backup. You just have to be sure that you are restoring the proper full backup/differential backup combination.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply