November 12, 2015 at 3:59 am
Completely hypothetical question.
If I have the following scenario:
1. 1st Nov 8am - Full backup
2. 1st Nov 12pm - Log backup
3. 1st Nov 12am - Log Backup
4. 2nd Nov 1am - Diff backup
5. 2nd Nov 8am - Full backup
6. 2nd Nov 12pm - Log backup
7. 2nd Nov 12am - Log Backup
8. 3rd Nov 1am - Diff Backup
Let say, I want to restore the database to 5pm on the 1st of November, BUT I only have the full backup (no.1) and the diff backup (no.4) available. I dont have any other backup files available.
Can I do:
Restore database Full with no recovery.
Then
Restore database diff with recovery, stop at Nov 1st 5pm.
Thanks.
November 12, 2015 at 4:38 am
No.
Differentials are like Full backups. You restore the backup using the STOPAT, then you can use logs to get to 5PM. If you use the differential, I think it'll error since the STOPAT is after the differentials end point. If it doesn't error, it'll just restore the whole differential to when it was taken (just don't remember what happens there). It won't stop any more than a full would. They're not a collection of transactions like a log backup. Instead it's a copy of pages of the database at a moment in time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 12, 2015 at 5:06 am
STOPAT is supported on full and diff restores only so that you can specify consistent options on all of your restore statements to reduce the chance of mistakes. To restore to the time you want, you need the two log backups. Then you'd do something like
RESTORE DATABASE <dbname> FROM DISK = <location> WITH NORECOVERY, STOPAT = '2015-11-01 17:00'
RESTORE LOG <dbname> FROM DISK = <location> WITH NORECOVERY, STOPAT = '2015-11-01 17:00'
...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2015 at 5:59 am
Thank you both. That's what I found when I ran a test, but couldn't understand why you could specify a STOP AT with a diff restore. You've explained that, so that clears it up.
Many thanks,
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply