I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by there are new data in the db so we cant do it at some random time in the middle of the night.

Is there any way to achieve this? I am pretty much able to do whatever I want so any suggestion will be useful.

  • Supposing you had the data duplicated identically at some particular nanosecond. How would you tell the client machines to stop using machine A and start using B? Oct 3, 2013 at 19:19

6 Answers 6


Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime. This is likely the fastest way of moving a database from machine A to machine B.

You can setup mirroring during the day with no downtime, then cutover at the time of your choosing. As long as the clients are programmed to retry whatever operation they are doing at the time of cutover, there will be no data loss (if you do the client correctly).


If your database structure allows for it you could look at peer to peer replication(PTP). You can use it to essentially build a two node active/active cluster. Once PTP replication is in place you will have two live copies of the database both able to accept new data while updating the other. This will allow you to switch over your applications with out having to take the database offline.

The tricky part of this is going to be getting your two databases in sync long enough to build up the PTP replication. There are also some caveats around your primary keys, specifically for any new rows. This will require that each table generates a unique key whether it be guid's, identity increments by 2 or more or building unique keys specific to the application. This in theory could allow for a no down time cut over but it will be tricky.

As others have posted, mirroring is a relatively quick and easy way to cut over. But it will require a few seconds of down time at least - this down time can be kept to a minimum if everything is scripted out appropriately.

  • 1
    Doing this is probably much more expensive/impractical to do it right than taking the hit of ~seconds of downtime using another method. +1 for a viable solution, though.
    – Jon Seigel
    Oct 4, 2013 at 2:58
  • I do agree, the amount of work to make this a viable solution likely will most likely out weigh the cost of taking the few seconds of downtime associated with a mirroring solution. Oct 4, 2013 at 17:30

You can't, sorry. Even in the case you redirect all attemps of new incoming connections to another server B, exactly the same as A (replicated or mirrored) at time t0, the on-going transactions on A will not exists on B until commited, and that is in a posterior time, t1. You always going to have downtime period, can be seconds or hours, but always exists.

See next post and info:



Hope this helps.

  • +1. There ARE databases that possibly could do that but then the next question - are you willing to pay for that ;) Seriously - plan your downtime proper.
    – TomTom
    Oct 3, 2013 at 20:30

Without knowing what kind of data was being input, I would say:

Box A -- Copy Structure and Routines -- > Box B
Box B: Use as Active Database
Box A --> Export Data 
Box B <-- Import Data (while still maintaining active database role)

The biggest wrench in the plan is going to be foreign key constraints while importing.

  • 1
    This will have more downtime + 60GB of data with import/Export is not a good idea. BCP is better if you are thinking of import/Export.
    – Kin Shah
    Oct 4, 2013 at 0:04

Considering the fact that "no downtime is allowed" and "we have only one machine" is a very flawed design, you might just as well prepare the new machine and say "what do you know, the old server just happened to crash, but look, the data is still intact, I can move it to the new server" :)

Sooner or later this is going to happen and it better happen when you want it to, not in the middle of an extended weekend.


Absolute no downtime but ridiculously expensive option.

Always on availability group from SQL enterprise. Setup a replica then fail over. Then remove from the availability group.

  • -1 There is no AlwaysOn in SQL 2008 Sep 20, 2018 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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