Detach/Attach SQL Database and Update Database Compatibility Level are two general tasks for the Database Administrator, both of which are described in this section.


Detach/Attach SQL Database


Database administrators can use any reasonable method for moving the database to the new server. The following steps outline completing this process using the Attach/Detach method.

  1. As always perform a backup before you do anything!


  1. Open SQL Server Management Studio and expand the Databases list.


  1. Right click on the database to be moved and select Properties



  1. On the Database Properties form select the Files page and scroll over to take note of the file path for the database to be moved as shown below


                                  


  1. Close the Database Properties form and return to the SQL Server Management Studio.


  1. Right click on the database to be moved and select Tasks/Detach.



  1. Make sure the database to be moved is selected on the Detach Database form and press OK as below.


  1. Once the detach operation is completed then move the database files to the correct location/path on the destination server. This includes the database.mdf file and the log.ldf file both.


  1. On the destination server open SQL Server Management Studio.



  1. Right click on Databases and select Attach.  

 


  1. On the Attach Databases form select Add


  1. Browse to find the server file that you copied into the correct location on the destination server and press OK as shown below.

  


  1. Ensure the correct database and its corresponding log file was added to the bottom of the Attach Databases form and press OK as below.        

       


  1. Once the attach operation is completed then return to the Server Management Studio, expand the Databases and ensure the moved database is included in the list.


  1. Pat yourself on the back for a job well done!



Update Database Compatibility


In the Database Properties window chose the “Options” page. Ensure that the database Compatibility level is set to SQL Server 2008 (100).