SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trust Differential with 30+ days since last Full backup


Trust Differential with 30+ days since last Full backup

Author
Message
Gary Hines
Gary Hines
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 1706
Hi All,
We are in a situation where we need to move a rather large database and the detach-copy-attach process looks to take 15-16 hours. We have a good full backup from 3/2 and transaction log backups since then. However, we were thinking (scary idea sometimes) of doing a diff backup, but were a little worried due to the length of time since the last full, and doing a full backup now looks like it may take about 30 hours. Has anyone had any success with performing a differential backup after so long? Based on the description it seems like it should work okay, but we'd like to get some opinions. Thanks for any advice.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150598 Visits: 39285
Depending on how much of the data has changed since 3/2 the differential could be just a large as a full backup.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Richard M.
Richard M.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2948 Visits: 2688
....an even scarier thought is such a long database recovery chain.... if any of the log backups in the interim is kaputt, you can only go back as far as the last full backup + recovered t-logs will bring you...that of course if the full backup is a-ok.

What size of DB are you dealing with here?

_______________________________________________________________________
For better assistance in answering your questions, click here
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224489 Visits: 19637
It sounds like you have an extremely large db. The diff should be fine, but keep in mind that without more full backups, you are risking data loss if something happens to that full. I might be sure I had 3 copies of that backup, and logs.

I'd also be tempted to stick in a diff a week if you have the space to do it. That might dramatically reduce recovery time if it comes to that.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Hines
Gary Hines
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 1706
Thanks for the replies. The database is about 550 Gb. We made a couple of strategic errors with the server, so we're looking to move this database off to another, better equipped server. Until we had contention problems we were doing weekly full, nightly diffs, and half hour t-log backups, but we had to make compromises due to contention on the server.

We're going to give the full backup plus t-logs a try. Hopefully that will get us fully restored, and if that doesn't work, we'll have to try the full-diff-recent t-log route.
Richard M.
Richard M.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2948 Visits: 2688
Gary,
what disks are those? 30 hours for 550GB seems to be EXTREMELY slow. We backup natively with SQL 1 TB in about 2.5 hours on our SAN.

We are also testing some 3rd party backup software, but even on the slow SATA disks it takes 4.5 hours for close to a TB and about 1.5 hours with compression done with these backup software.

You might want to check into this option as well

_______________________________________________________________________
For better assistance in answering your questions, click here
Gary Hines
Gary Hines
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 1706
It's an older Dell SAN model. That's why we're moving this database to a new server. We've noticed that when we do almost anything slightly intensive with this database we start to get WRITELOG waits, and the number of waiting tasks starts to really climb. Hopefully, once we get this database moved we'll be able to work on both servers and get them more aligned with best practices.
homebrew01
homebrew01
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20012 Visits: 9222
If you look at the sizes of your t-logs since the last full, would that help give you an idea of the size of a new diff ? Not that you could calculate it, but if all your t-logs are tiny, you might want to try a diff. If you have lots of massive t-logs, then maybe not ?
Just thought it might help with the decision.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224489 Visits: 19637
T-Log backups is a good idea if you are getting lots of new data or changing new rows. If you have lots of changes to existing rows, you could have GBs of T-log backups, but the diff would be small since your total changes would be low.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search