January 23, 2018 at 3:54 pm
SQL 2014
My colleagues and I have been debating whether a Differential or Transactional backup (.bak, .trn) can be restored without first restoring a full backup with NORECOVERY.
Here is our scenario (what we would like to take place):
Sunday - Full DB Restore > Monday - Differential Restore > Tuesday - Differential Restore > Wednesday - Differential Restore > Thursday - Differential Restore > Friday - Differential Restore > Saturday - Differential Restore > Repeat
Is this scenario possible, or must EVERY Differential restore have its own full restore beforehand? If this scenario is not possible through SSMS, is there third party software that can accomplish this?
Thank you all for your help.
Dan Tuma
January 23, 2018 at 4:05 pm
Daniel Tuma - Tuesday, January 23, 2018 3:54 PMSQL 2014
My colleagues and I have been debating whether a Differential or Transactional backup (.bak, .trn) can be restored without first restoring a full backup with NORECOVERY.
Here is our scenario (what we would like to take place):
Sunday - Full DB Restore > Monday - Differential Restore > Tuesday - Differential Restore > Wednesday - Differential Restore > Thursday - Differential Restore > Friday - Differential Restore > Saturday - Differential Restore > Repeat
Is this scenario possible, or must EVERY Differential restore have its own full restore beforehand? If this scenario is not possible through SSMS, is there third party software that can accomplish this?
Thank you all for your help.
Dan Tuma
Could you expand on what you are trying to accomplish?
January 23, 2018 at 10:45 pm
To expand: We have three databases that are hosted by a company that is offsite. We also have two onpremis servers that have copies of these three databases. We use these as development, testing, reporting and ETL processes.
Each night we download a daily backup of the three databases and restore them onto our onpremis servers.
The issue that we are facing is that one of the DBs is 120GB in size. When compressed it is still 20GB large. The problem is that the SFTP download in combination with the restore process takes anywhere from 5 to 7 hours to complete on each server. This accounts for most of the night and leaves very little room before start of business to schedule reports and ETL processes on those servers.
We want to use the full/differential scenario to reduce the size of the download and the time to complete the download and restores of the three databases.
So far, everything I have read from Microsoft indicates that a full backup must be restored before any differential or transactional restore can take place. In other words, the above mentioned scenario of Full, Differential, Differential, Differential... cannot be done. The scenario would actually have to be, Full, Full/Diff, Full/Diff, Full/Diff... Which would obviously be worse than what we are currently doing.
Have I got this all wrong? Can we do Full, Diff, Diff, Diff...?
Hope I didn't get too wordy.
Dan Tuma
January 24, 2018 at 1:36 am
Latest Full, followed by latest Diff - How the Differential backup Works - Technet
January 24, 2018 at 2:39 am
Daniel Tuma - Tuesday, January 23, 2018 10:45 PMHave I got this all wrong? Can we do Full, Diff, Diff, Diff...?
You can, providing all those diffs are based off the same full backup. Take another full backup on the source, and you'll no longer be able to restore diffs (.trns will be fine though, that's the way log shipping works).
Oh, and all restores have to be WITH STANDBY, leaving the DB read only.
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
January 24, 2018 at 4:37 am
GilaMonster - Wednesday, January 24, 2018 2:39 AMDaniel Tuma - Tuesday, January 23, 2018 10:45 PMHave I got this all wrong? Can we do Full, Diff, Diff, Diff...?You can, providing all those diffs are based off the same full backup. Take another full backup on the source, and you'll no longer be able to restore diffs (.trns will be fine though, that's the way log shipping works).
Oh, and all restores have to be WITH STANDBY, leaving the DB read only.
Gail, I know you'll correct me when I'm wrong (😛), but while you can do Full, Diff, Diff, Diff, why would you want to? Isn't that third Diff the accumulation of all the other changes too? Any one Diff contains all the changes since the last Full, or am I completely off the mark here. From a Recovery Time Objective, I'd think that Full and then the last Diff is going to be faster than Full, Diff, Diff, Diff, even though you arrive at the same location.
"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
January 24, 2018 at 5:28 am
Grant Fritchey - Wednesday, January 24, 2018 4:37 AMGilaMonster - Wednesday, January 24, 2018 2:39 AMDaniel Tuma - Tuesday, January 23, 2018 10:45 PMHave I got this all wrong? Can we do Full, Diff, Diff, Diff...?You can, providing all those diffs are based off the same full backup. Take another full backup on the source, and you'll no longer be able to restore diffs (.trns will be fine though, that's the way log shipping works).
Oh, and all restores have to be WITH STANDBY, leaving the DB read only.Gail, I know you'll correct me when I'm wrong (😛), but while you can do Full, Diff, Diff, Diff, why would you want to?
Poor man's log shipping (ie while in simple recovery) with the DB in standby between restores. Means it's readable and still available for more restores.
Implication is that the diff restores aren't one immediately after the other.
If the DB's in full recovery, rather restore the log backups.
I'm pretty sure you can do that, haven't personally tried it (log shipping in standby is what I've done)
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
January 24, 2018 at 5:35 am
GilaMonster - Wednesday, January 24, 2018 5:28 AMPoor man's log shipping (ie while in simple recovery) with the DB in standby between restores. Means it's readable and still available for more restores.
Implication is that the diff restores aren't one immediately after the other.If the DB's in full recovery, rather restore the log backups.
I'm pretty sure you can do that, haven't personally tried it (log shipping in standby is what I've done)
Ah, well, it's a choice. Not one I'd want to pursue.
"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
January 24, 2018 at 5:42 am
Grant Fritchey - Wednesday, January 24, 2018 5:35 AMGilaMonster - Wednesday, January 24, 2018 5:28 AMPoor man's log shipping (ie while in simple recovery) with the DB in standby between restores. Means it's readable and still available for more restores.
Implication is that the diff restores aren't one immediately after the other.If the DB's in full recovery, rather restore the log backups.
I'm pretty sure you can do that, haven't personally tried it (log shipping in standby is what I've done)
Ah, well, it's a choice. Not one I'd want to pursue.
I wouldn't either, since each new full backup means resetting the whole thing.
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
January 24, 2018 at 5:43 am
To be clear, Daniel, what you probably want to do is, each night download the .trn files from that day, and restore them in order WITH STANDBY. That'll give you a read-only copy if the DB as-of the end of the previous day.
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
January 24, 2018 at 10:04 am
Gail,
Again, so that I am clear, the best solution for our situation is:
Sunday - Full, Monday - trn, Tuesday - trn, Wednesday - trn, Thursday - trn, Friday - trn, Saturday - trn. ... Repeat on Sunday
AND, are the following statements correct?
1. The restored DB will be Read Only because it stays in STANDBY Mode
2. The host company of our live database cannot perform any other backups without resetting our trn schedule, forcing us to perform a FULL backup restore.
3. If our hosting company uses 3rd party software to backup the SANs where the DB resides, will this reset our backup trn schedule?
Thank you Gail and all who weighed in on this post. Your help has been indispensable.
Dan Tuma
January 24, 2018 at 11:26 am
Yes, no, no
They just must not take their own transaction log backup.
Edit: And you can carry on just restoring the log backups on the sunday, the following week and after.
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
January 24, 2018 at 4:25 pm
Thank you all for your input. I think I can safely say that I would be speaking for all users of this site when I say I am very appreciative of the fact that you sacrifice your valuable time to read and post on our SQL issues.
Dan Tuma
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply