SQL Server update and database transfer

Question

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?

Answer 1

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.

Answer 2

I agree with Kevin's accepted answer and I wanted to add another perspective on the detach/attach vs backup/restore part. You can see more in this question on In Place vs Migration for SQL Server Upgrades where I wrote a section in my answer discussing this same question.

Basically... I tend to almost always prefer backup/restore for a few reasons but they mostly resolve around availability, the ability to rollback and safety. When detaching, it is too easy to accidentally delete, modify, overwrite or otherwise harm a SQL Server data file or log file which will make attaching difficult on any server.

Now you can mitigate that by taking a backup before detaching for the upgrade or migration (and you should!) and that will help you but it also adds steps and if everything that can go wrong does go wrong during your upgrade, you could theoretically be in a bad spot.

The biggest benefit of detach/attach is you save some time, but for me that benefit is usually outweighed by my sense of the risks and challenges.

I think for this question it sounds like it was an in place upgrade and, as Kevin rightly said in his answer I +1d, you don't need to worry about this. But those are some considerations to make on detach/attach as well.