March 4, 2007 at 6:06 am
I've been trying to figure this problem out for the past few days. I have a production server and a standby server. There's a job on the standby server that creates a full database backup and stores it to my standby server. I also have another job on the production server that runs a differential database backup, and also stores it on the standby server. The full backup is run 3 times a week (Mon, Wed, Sat). The differential is run every hour.
On the standby server, I have a job that restores the full database backup, and I don't have any problems with it. I'm running into trouble when the restore for the differential database is run. The following is the script that I've written:
RESTORE DATABASE XYZ
FROM DISK '...\differential_database.BAK'
WITH RECOVERY
The error message that I get is: The presiding 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.
Please note that the full database is run WITH NORECOVERY. Also I only have one file for the differential and its overwritten everytime it is taken. If someone could help me with this it would be great...thanks in advance.
March 4, 2007 at 8:14 am
From Books on Line:
SQL Server requires that the WITH NORECOVERY option be used on all but the final RESTORE statement when restoring a database backup and multiple transaction logs, or when multiple RESTORE statements are needed (for example, a full database backup followed by a differential database backup).
The sequence of SQL statements should be:
-- Each time a full backup is made, a full restore needs to be performed
Restore database xyx from disk = 'full_backup.bak'
WITH NORECOVERY , replace
--then for each differential:
Restore database xyx from disk = 'differential_backup.bak'
WITH NORECOVERY
SQL = Scarcely Qualifies as a Language
March 4, 2007 at 11:20 pm
Thanks Carol, it worked!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply