SQL Server update and database transfer

In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What is the best way to transfer the database during/after SQL Server update?

If you are upgrading in place, you shouldn't need to transfer any databases; the instance will retain information on databases.

If you are installing a new named instance or installing it fresh on a different server, you have two major options: deatch-and-attach, and backup-and-restore.

The detach-attach option is pretty quick: detach the current database using sp_detach_db or in SSMS, right-click on the database and go to Tasks --> Detach. Then, you can cut and paste the database files to their new location. On the new instance, use sp_attach_db or in SSMS, right-click the Databases folder and select "Attach..." from the menu options.

The backup and restore option is pretty simple: take a backup of the current database. Then, copy that backup someplace where the new instance can see it, and restore from that backup. You'll have a copy of the current database on the 2008 instance, as well as on the 2005 instance.

Here are some considerations:

• If you need the 2005 database up during this time, use backup-and-restore.

• The larger the database, the more likely you'd want to detach and attach, especially if this is staying on the same server and you're under a time crunch. Taking backups and restoring from backups can take longer than shuffling bits across the WAN (or, best-case, updating a file pointer in Windows). It also will mean more disk space in use, as backups take up some non-zero amount of space.

• If you are currently using replication, you can't detach until you disable publishing (as the BOL link above indicates).

• If you currently have any snapshots of the database, you can't detach until you drop all snapshots. If you don't want to drop those snapshots, go with the backup and restore method.

StackExchange Database Administrators Q#29596, answer score: 5