September 1, 2015 at 6:13 pm
Can I use a full and differential backup to restore to a point of time?
Or I have to use full and transaction log backups in order to do a point of time restore?
I found today when I tried to restore a db from another database at the point of time for example 3:10 pm,
SSMS automatically select the full backup + the transaction backup that is done at 3:00 pm, but not select full + the differential backup I did at 3:12pm.
So I lost those records entered after 3:00pm.
I supposed it should use the differential backup and restore to 3:10. but it didn't.
September 2, 2015 at 1:39 am
It wont be the first time the GUI plays with you. I suggest TSQL to recover rather than relying on the GUI. What you asked for ( If I read correctly ) should be fine.
September 2, 2015 at 2:43 am
sqlfriends (9/1/2015)
Can I use a full and differential backup to restore to a point of time?
No. Full and diffs can only be restored to the time which they ran (end of). The STOPAT is ignored for those backups as it's not valid.
GUI's fine for simple restores. For anything more, use T-SQL. You can always get the GUI to script the restores and you then tweak.
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
September 2, 2015 at 5:29 am
If you add the next log backup, you should be able to get to 3:10.
"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
September 2, 2015 at 10:16 am
That is what I found. My differential restored to the end of file.
Is this documentated in Microsoft book? if not, It is misleading on SSMS it says to restore to point of time, and automatically choose the file, but apparently it doesnot work for differentials.
September 2, 2015 at 10:17 am
That is good to know, so I can do another log backup after the previous diffential, and can still restored to 3:10?
That really helps.
September 2, 2015 at 10:41 am
sqlfriends (9/2/2015)
That is good to know, so I can do another log backup after the previous diffential, and can still restored to 3:10?That really helps.
Yes. Differentials are tied to the last full backup. Log backups, except the very first one after changing the database recovery model, are completely independent of any backup, full or differential. That's why they're so terribly important a part of disaster recovery.
"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
September 2, 2015 at 6:20 pm
Just notice this, you said transaction backup is fully independent even not on full backup? (except that one case you mention).
I thought we will always need a full first as the base for the restore., the transaction backup or differentical backup on top of it?
Thanks
September 2, 2015 at 7:56 pm
sqlfriends (9/2/2015)
Just notice this, you said transaction backup is fully independent even not on full backup? (except that one case you mention).I thought we will always need a full first as the base for the restore., the transaction backup or differentical backup on top of it?
Thanks
But the logs are not tied to a particular full backup. Let's say you have a full backup every 12 hours. You also have log backups every 15 minutes. Now, let's say you want to restore to a point in time today at 3pm. We could use our original full backup from the day before and then 39 hours worth of logs. Or, we could use the noon full backup and then 3 hours worth of logs. The full backups and the log backups are not connected.
Let's say we also have differentials running in the six hours between the log backups. Each differential is connected directly to the last full backup. No choices, options. If we wanted to restore to 7am, we could use the midnight full and the 6am differential. We couldn't use the full from noon the previous day because there was another full taken between the two.
I hope that helps to clarify a little.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply