SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential Restore


Differential Restore

Author
Message
rtimblin
rtimblin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 5
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
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24304 Visits: 14905
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Steve Jones
Steve Jones
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: Administrators
Points: 83527 Visits: 19223
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
rtimblin
rtimblin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 5
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?
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24304 Visits: 14905
In your case your script should do this:


    1. Sunday after the full backup a Full Restore with Recovery
    2. Monday - Saturday
    1. Restore the Sunday Full Backup with Norecovery
    2. Restore the daily differential Backup with Recovery




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
rtimblin
rtimblin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 5
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?
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24304 Visits: 14905
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ALZDBA
ALZDBA
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16034 Visits: 8971
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search