April 12, 2010 at 11:30 am
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.
April 12, 2010 at 11:47 am
Depending on how much of the data has changed since 3/2 the differential could be just a large as a full backup.
April 12, 2010 at 1:54 pm
....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?
April 12, 2010 at 2:07 pm
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.
April 12, 2010 at 2:13 pm
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.
April 12, 2010 at 2:19 pm
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
April 12, 2010 at 2:30 pm
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.
April 12, 2010 at 3:25 pm
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.
April 12, 2010 at 5:42 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply