Differential Restore

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » In The Enterprise » Differential Restore

Differential Restore

Rate Topic

Display Mode

Topic Options

Author

Message

rtimblin

Posted Thursday, September 11, 2008 7:29 AM

Forum 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

Jack Corbett

Posted Thursday, September 11, 2008 8:20 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Steve Jones - SSC Editor

Posted Thursday, September 11, 2008 9:08 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 2:36 PM
Points: 31,432, Visits: 13,743

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

rtimblin

Posted Thursday, September 11, 2008 9:24 AM

Forum 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

Jack Corbett

Posted Thursday, September 11, 2008 10:13 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

In your case your script should do this:

Post #567903

rtimblin

Posted Friday, September 12, 2008 6:58 AM

Forum 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

Jack Corbett

Posted Friday, September 12, 2008 7:09 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #568457

ALZDBA

Posted Friday, September 12, 2008 7:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't 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" BigGrin

Need a bit of Powershell? How about this

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

Post #568487

« Prev Topic | Next Topic »

Permissions