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 the current SQL Server's highest level.




Other Consideration

Server Logins


Server level security permissions will not move with the database. Additional scripts or manual setup may be needed to establish the server logins on the new server.


Server Logins may be setup on an individual basis or using Active Directory Groups. Please consult the Microsoft SQL Server documentation for the steps needed to add new Server Logins.