We are transitioning a large amount of data, in the form of a SQL database, from one location to another and the data transfer speeds are very slow but we want to cause as short of an interruption of services to our customers as possible.

The idea we landed on was to stand up SQL on the destination server, transfer a full backup, which could take several days, prepare the new SQL for live operation then transfer and restore an incremental backup of the interim period before setting it to go live.

I've never worked with incremental backups in SQL before. Should this suit our purposes without any loss of data or is there more to it that I'm missing?

Thank you in advance for any explanation.

And as a follow-up, our current automated backup strategy is Microsoft Data Protection Manager. I know incrementals are only good since the last full backup. When DPM creates a backup does that become labeled in SQL server as the last known full backup so the incremental will only backup transactions since the last DPM backup or are the two completely autonomous?

  • Why don't you just use log shipping? – longneck Mar 2 '15 at 16:28
  • We tried that and we were taking on transactions faster than we could ship them. So the number of un-handled transactions would just grow until the service stopped. Also, I'm not super well-versed on SQL but just backing up transaction logs would still leave us with other data that needed to be moved, no? – Tony Mar 2 '15 at 16:58
  • Then what makes you think that an incremental backup will work any better? The transaction logs are what constitutes an incremental backup, and are also the logs that are shipped in log shipping. (This is an oversimplification but accurate enough for this conversation.) – longneck Mar 2 '15 at 17:00
  • Our plan is to move the full backup, about 200GB to the new server, this will take, due to the distance away of our new hosted environment, somewhere between 1-3 days. Then our plan is to take the application down, create and transfer an incremental backup which should be considerably smaller and shorter amount of time. Then "flip the switch" to make the new environment live. Live transfering of the transactions though did not work for the reasons stated above. – Tony Mar 2 '15 at 17:19
  • You missed the point of my comment. Log shipping and incremental backups are (effectively) the same size. If you couldn't keep up with log shipping, then you won't be able to send incremental backups fast enough, either. – longneck Mar 2 '15 at 17:22

Since your plan is to shut down the system during the time it takes to send the last incremental backup, then your plan is good. Just make sure to use the NORECOVERY option when you apply all of the backups except for the last incremental.

  • Thanks longneck. Any information on my follow up question about DTM? – Tony Mar 3 '15 at 14:18
  • I don't use DPM, so I can't answer that question. Sorry. – longneck Mar 3 '15 at 14:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.