Hello, I need to migrate a database from a SQL Server 2005 instance, to a SQL Server 2008 instance which is named differently.
Is it as simple as detaching the 2005 DB, and moving to the 2008 server, followed by a reattach?
Do I also need to run "sp_removedbreplication" given that the SQL 2008 instance uses a different name than to SQL 2005 instance?
Any other suggestions or concerns?
Thanks!
I assume you are following the guidelines here. One thing that they don't mention is the issue of orphaned users. You may need to fix those using the process outlined here. Also, be sure to take a backup of the database before you detach. If you are going to set the compatibility level to 100, you might want to examine any deprecated features that you may have been using that could break in SQL 2008.
I prefer backup/restore method over detach/attach. Detach/attach requires more downtime as you have to wait for the copy to complete. On a large database, you are talking hours of downtime. Backup/restore can be prepped in advance and requires just minutes or perhaps even seconds of downtime if done properly.
Does the 2005 system use replication? Will you be using replication on 2008?
I have been weary of attach/detach as well. I have only experimented with attach and detach in DEV environments and have not used in any production work. My fear (perhaps unfounded) is that I might not be able to attach the database after I detach it (even to the original server).
Yeah that's definitely a concern. But my main reason is the downtime. It's just unnecessary downtime waiting for the copy when backup/restore allows you to prep and have very little downtime. Now if the database is <1GB, then fine detach/attach is okay provided you backup before the detach to reduce risk.
Thanks All, detach/attach worked fine for me. It was a non production DB being moved to a different server.