Prev: C API
Next: Design question
From: aj on 17 Feb 2010 15:53
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





From: Erland Sommarskog on 19 Feb 2010 12:47
aj (ronald(a)mcdonalds.com) writes:
> 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.

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: C API
Next: Design question