SQL Server 2005 SP2 9.0.3054

I would like to move my DB (with Full Recovery Model) onto a
/different/ server. I would like to do this w/o losing any
transactions.

My question relates to when it is appropriate to:

1. restore my last DB backup onto the destination server, then roll
forward any logs that were backed up subsequent to that backup

Versus

2. detach the database from the source server, then attach at the
destination server

Note that I want to move my database, so its alright if it disappears
from the source server. Note also that I might want to change the
location of MDF/LDF files.

I can see how if you have a really large database, and a short
maintenance window, it might make sense to restore a backup onto the
destination server beforehand, then roll forward logs onto it right
up until the maintenance window.

But whats if its a smaller database that can be easily detached and
then attached during a maintenance window? When would I pick one
approach over the other? Does one method do a more complete/thorough
job of placing the new database into the instance?

Any thoughts appreciated.

TIA

aj

aj (ronald (AT) mcdonalds (DOT) com) writes:
Personally, I prefer BACKUP/RESTORE, because it is one less file to copy
around. In your case you could set the database into single-user more before
you back it up, so that on one strays there by mistake.

There is no problem with changing the location of the files with RESTORE,
use the WITH MOVE option.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx