Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trust Differential with 30+ days since last Full backup Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 11:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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.

Post #901799
Posted Monday, April 12, 2010 11:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 23,076, Visits: 31,603
Depending on how much of the data has changed since 3/2 the differential could be just a large as a full backup.



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)
Post #901819
Posted Monday, April 12, 2010 1:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:40 AM
Points: 1,093, Visits: 2,615
....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
Post #901926
Posted Monday, April 12, 2010 2:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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
Post #901937
Posted Monday, April 12, 2010 2:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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.
Post #901950
Posted Monday, April 12, 2010 2:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:40 AM
Points: 1,093, Visits: 2,615
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
Post #901957
Posted Monday, April 12, 2010 2:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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.
Post #901973
Posted Monday, April 12, 2010 3:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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.



Post #902015
Posted Monday, April 12, 2010 5:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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
Post #902072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse