September 11, 2008 at 7:29 am
We want to do a differential restore each evening via script from one database to another. The script we are using is below. Also, the error we get is below. We cannot afford to due a full restore each evening also which would negate the reasoning behind a partial/differential backup/restore.
ERROR:
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence
SCRIPT:
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE xxx
FROM DISK='D:\xxx\xxx.bak'
WITH
MOVE 'DATA01' TO 'F:\xxx\data\xxx_Data.mdf',
MOVE 'LOG01' TO 'F:\xxx\log\xxx_Log.ldf',
RECOVERY
GO
ALTER DATABASE xxx SET MULTI_USER WITH NO_WAIT
GO
September 11, 2008 at 8:20 am
You cannot just restore from differential backup. You need to do a full restore with norecovery first then restore the differential with recovery.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2008 at 9:08 am
Jack is correct. The diff stores changes SINCE the full. So it doesn't have a baseline on which to work. A diff is used to reduce the number of log restores you need to do, not prevent you from restoring a full backup.
September 11, 2008 at 9:24 am
In the scenario, we would do a full on Sunday night and a differential each weekday and Saturday. However, it sounds like after you do the full, the database would be left in a READONLY state, in order to allow differential restores to be done each night; which foils what we wanted to do, if I understand it correctly. Is that correct?
September 11, 2008 at 10:13 am
In your case your script should do this:
2. Monday - Saturday
1. Restore the Sunday Full Backup with Norecovery
2. Restore the daily differential Backup with Recovery
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2008 at 6:58 am
Regarding the Monday through Saturday full restore with NORECOVERY; will it actually do a restore (hence taking 4+ hours) or does it merely set it up for the partial and hence complete quickly?
September 12, 2008 at 7:09 am
It does the restore (4+hours). That's the only way to do a differential restore is to base it on a Full that was run without Recovery.
If you need to have a copy of the database daily have you looked at REPLICATION? If it's a daily thing you could look at using SNAPSHOT replication on a daily basis.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2008 at 7:34 am
If you log load isn't that much, you may want to consider making a full backup and use log backups.
Then you can restore the full backup (norecovery) (first time) and a first log backup with STANDBY to a file.
This db will then be read only ! and you can issue subsequent log- restores to new standby file.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply