Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Differential Restore Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:05 AM
Points: 7, 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
Post #567705
Posted Thursday, September 11, 2008 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #567757
Posted Thursday, September 11, 2008 9:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #567833
Posted Thursday, September 11, 2008 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:05 AM
Points: 7, 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?
Post #567844
Posted Thursday, September 11, 2008 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #567903
Posted Friday, September 12, 2008 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:05 AM
Points: 7, 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?
Post #568448
Posted Friday, September 12, 2008 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 10,381, Visits: 13,436
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

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
Post #568457
Posted Friday, September 12, 2008 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:03 AM
Points: 6,748, Visits: 8,544
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #568487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse