Recently I was asked to move a database from SQL Server 2000 to a SQL Server 2005 instance. This of course creates two questions. What process will I use to move the data and two, why not SQL Server 2008. I will spend a little bit of time explaining the best two options to move a database. Otherwise for the second question, I’m a SQL Lumberjack. I use the axe I’m giving.
The two options I think are the best are either to restore your database from the most recent full back up or a detach/attach process. Both of these options have their pros and cons. In fact as a SQL Lumberjack you may not have a choice. The customer may set forth requirements that force you to go one way or the other. However, as a DBA you are the “Default Blame Acceptor” if something goes wrong the finger will be pointed at you. Don’t be afraid to say something to provided “constructive criticism” to your manager. Any DBA has to be able to manage their manager. A manager needs to be a DBA’s friend not an enemy. It is important for a DBA to create and maintain good working relationship with their manager, and with another other managers in the organization. Your manager hired your for your expertise and your professionalism. If you believe one process is better than that set forth by the customer, then explain that to your manager and let them know you would be happy to give a presentation to the customer to prove your point. In the end it will be a “value add” to the customer and your department for showing concern for the client.
Option 1: To restore from a Full Backup. This is also an excellent opportunity to truly know if your backups are successful backups.
This option is a good option if you are moving the database during working business hours. This is ONLY if you are moving to a new server that is not in production. Once the database is restored on the new SQL 2005 Server you will want to Update the Compatiblity Level and Update Statistics. We need update the statistics because statistics are not automatically updated during the conversion process. If you don’t manually update the statistics, then you may find that our queries are running slowly because of incorrect statistics. This will also bring your indexes to a SQL Server 2005 level.
You will want to change the compatibility level and update statistics regardless either move option.
Option 2: Running the Detach and Attach process.
This is I think the best option if you will be doing the move during off hours. The reason you will do it during off hours is because you will be dropping the database from the SQL 2000 instance and therefor taking it offline. Whereas with the Restore from Backup option you can simply take you’re most recent full backup and restore it to the new server that is NOT the production box yet. Before you run the detach process be sure as always before you do anything big to a database, you have a good backup. Now preform the following pre-detach steps.
- Execute the following query to ensure no processes of the Said Database are running
select * from sysprocesses a
inner join sysdatabases b on a.dbid=b.dbid
- If this displays any running DatabaseName processes they will need to be killed prior to detatching the database.
- Document the SPID number
- Execute KILL 53 (53 is an example of a SPID#)
- Execute the following querys to ensure no active user is running a DatabaseName process
select * from sysdatabases
- Recored the dbid for DatabaseName
EXEC sp_who ‘6’ <– The ‘6’ specifies the procees ID
- If a login name is displayed with user name or Domain/USERNAME that person will need to be notified that DatabaseName will be dropped and they need to save all work.
- Expand Databases and select DatabaseName.
- If the database is in use, restrict access to a single user:
- Right-click the database name and point to Properties.
- In the Select a page pane, select Options.
- In the Other options pane, scroll down to the State options.
- Select the Restrict Access option, and in its drop-down list, select Single.
- Click OK.
A message box appears to inform you that this action will close all connections to the database. To proceed, click OK.
You are now ready to run the detach and attach process. Be sure to update the ODBC drivers’ as well.
Feel free to email me or comment on this post if you would like more details steps or scripts for each of these two options.