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.
- As always perform a backup before you do anything!
- Open SQL Server Management Studio and expand the Databases list.
- Right click on the database to be moved and select Properties
- 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
- Close the Database Properties form and return to the SQL Server Management Studio.
- Right click on the database to be moved and select Tasks/Detach.
- Make sure the database to be moved is selected on the Detach Database form and press OK as below.
- 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.
- On the destination server open SQL Server Management Studio.
- Right click on Databases and select Attach.
- On the Attach Databases form select Add
- Browse to find the server file that you copied into the correct location on the destination server and press OK as shown below.
- Ensure the correct database and its corresponding log file was added to the bottom of the Attach Databases form and press OK as below.
- 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.
- 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).
