October 26, 2004 at 4:52 pm
I'm trying to use restore differential backup, I already did the full backup restore then trying to run the code below:
got an error msg "The prceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE DATABASE is terminating abnormally. IN SQLSERVER 2000 IN WINDOWS 2003 SERVER. ANy ideas???
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
NORECOVERY
------------------------------------------------------------------------
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff' WITH FILE = 1,
NORECOVERY
------------------------------------------------------------------------
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH
REPLACE,
-- NORECOVERY, -- Use if more T/Logs to recover
RECOVERY, -- Use if no more T/Logs to recover
STATS = 10, --Show progress (every 10%)
MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
GO
October 26, 2004 at 10:54 pm
Restore the full database backup using the NORECOVERY or STANDBY options if you want to further apply differential or log restores e.g.
RESTORE DATABASE leon FROM .... WITH NORECOVERY
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 27, 2004 at 4:41 pm
Cheers Peter! I will try it out!
October 27, 2004 at 5:42 pm
Got another error msg:
Cannot apply the backup on device
C:\DB\Leon_diff_backup.diff
My code below:
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_db_200410270115.BAK'
WITH NORECOVERY
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
NORECOVERY
Any ideas?
October 27, 2004 at 8:26 pm
You can't use the WITH MOVE option when restoring differential backups. Also, you might try running the RESTORE HEADERONLY for both backups, and check if the CheckPointLSN for the full backup matches the DifferentialBaseLSN for the differential backup.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
November 2, 2004 at 12:02 pm
It match Peter...
The CheckPointLSN of the full backup matches the DifferentialBaseLSN of the differential backup.
I tried this but still no luck...
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_db_200410290115.BAK'
WITH NORECOVERY,
MOVE 'LEON_Data' TO 'C:\DB\LEON_Data.MDF',
MOVE 'LEON_Log' TO 'C:\DB\LEON_Log.LDF'
RESTORE DATABASE LEON
FROM DISK = 'C:\DB\LEON_diff_backup.diff'
WITH FILE = 2,
RECOVERY
error msg: Server: Msg 4038, Level 16, State 1, Line 7
Cannot find file ID 2 on device 'C:\DB\LEON_diff_backup.diff'.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
November 3, 2004 at 1:26 am
The error assumes that the 'C:\DB\LEON_diff_backup.diff' is being used as destination file to differential backup where the file is used more than once as your destination file. Meaning, the setting of the destination file in your differential backup is set to 'Append to media' in order to use the file number. If the setting is set 'Overwrite existing media', you don't have to specify the 'file' option.
November 17, 2005 at 2:57 pm
I'm having the same problem.
If i'm reading this correctly, I would run the restore like so,
RESTORE DATABASE Northwind
FROM DISK = '\\server\c$\filename_20051116.DIF'
-- WITH FILE = 1,
GO
I've tried it with the "with" clause and without, but still get the same error message,
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device '\\server\c$\filename_20051116.DIF' to database 'Northwind'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I noticed that the checkpointLSN and baseLSN between the full and dif don't match, I assume they should?
Should I do another diff or what?
April 9, 2008 at 6:29 pm
The reason i think is, there might be a full another full backup taken between the database you are restoring and differential backup.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply