1

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?

Tony
  • 457
  • 3
  • 11
  • 23
  • Why don't you just use log shipping? – longneck Mar 02 '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 02 '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 02 '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 02 '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 02 '15 at 17:22
  • Our system will be taken down before taking the final incremental and when we come back up we will be on the new system with all of our data. If we keep the system live and do log shipping the transactions come in faster than they can ship. That's why the method of an incremental backup is possible but not log shipping. – Tony Mar 02 '15 at 17:39
  • @longneck at the risk of hand-waving, the performance difference between theory & practice can be significant, but I get what you're saying. Tony - if you have considerable time and a large budget, GoldenGate (an Oracle acquisition) does this (disclaimer: I worked there but no longer do). You can quickly prototype a solution (for free) by downloading from OTN and experiment with theoretical max transfer rates. – michael Mar 02 '15 at 18:27
  • Unfortantely Michael we do not have considerable time or money :( – Tony Mar 03 '15 at 14:19

1 Answers1

0

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.

longneck
  • 23,082
  • 4
  • 52
  • 86