I need to move a 50GB SQL 2005 database to a new server that is about
a thousand miles away, and I only have about 12 hours of down time in
which to get it done.  I'm looking for the best way to do this.  Set
up replication before hand and let the source replicate to the new
server over time?  Use log shipping?  Use mirroring in some way?

Thanks.

I'd probably do it with some type of 'log shipping', i.e. copy the the full
database backup before hand, restore it in nonercovery mode on the target,
and then keep copying/restoring the log backups. By the time you have your
down time window, you only need to backup, copy, and restore the last part of
your database log, which hopefully is not too big. In addition, if you don't
want to copy a large log backup file, you can always set up to backup the log
more frequently.

Check what kind of file copy throughput you can get from your primary source
to the target, this would give you an estimate as to how long the file copies
will take, and help you plan things out.

Linchi

Show quoteHide quote

Stephen,

You might find this article of use:
http://vyaskn.tripod.com/moving_sql_server.htm
He also covers many issues about making sure that once you have moved the
database it has all the supporting jobs, shares, (logins need to be included
as well), and other components.

You will notice that he discusses using log shipping a little over halfway
down.  This is not the automated version, but a more manual version that you
use for the move. Following this advice, you might:

1. Restore a backup of your database to the new server either using WITH
NORECOVERY or WITH STANDBY.  (You may need to physically ship the backup to
the new site, depending on the bandwidth that you have between the
computers.)

2. Restore all the log backups from the original server to the new server
also WITH NORECOVERY.  I assume in this case that you have the bandwidth to
restore the log files online to the new server.

Once you are done, do the final RESTORE using WITH RECOVERY and go through
whatever final setup is needed to go live again.

RLF


Show quoteHide quote

Stephen
Yes , the 'set up' is pretty clear , full backup copy  /restore on the
target and then start copying /restore log backups.

I wish you network between two sites will be more than good:-)


Show quoteHide quote

You may also want to invest in a backup compression third party system.  I
recommend Hyperbac, although there are several other vendors out there as
well.

Show quoteHide quote

strange BLOB beahaviour
AWE on SQL Server2005
ID large queries while they execute and kill
'Generate scripts' functionality in SQL 2005 not being consistent
Problem with SUM - Help please
problem with new login
Saving images
Column Limits
Problem with triggers an ntext
SQLhas encountered 2 occurrence(s) of IO requests taking longer th