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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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:
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.