1. Home
  2. Knowledge Base
  3. DataLog
  4. Move Guide – Database Administrator Tasks
  1. Home
  2. Knowledge Base
  3. Enterprise
  4. Move Guide – Database Administrator Tasks

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!
  2. Open SQL Server Management Studio and expand the Databases list.
  3. Right click on the database to be moved and select Properties
  4. 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                                         DL-Admin DB Tasks 1
  5. Close the Database Properties form and return to the SQL Server Management Studio.
  6. Right click on the database to be moved and select Tasks/Detach.
  7. Make sure the database to be moved is selected on the Detach Database form and press OK as below.DL-Admin DB Tasks 2
  8. 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.
  9. On the destination server open SQL Server Management Studio.
  10. Right click on Databases and select Attach.   DL-Admin DB Tasks 3
  11. On the Attach Databases form select Add
  12. Browse to find the server file that you copied into the correct location on the destination server and press OK as shown below.                                                                                                                                                                 DL-Admin DB Tasks 4
  13. Ensure the correct database and its corresponding log file was added to the bottom of the Attach Databases form and press OK as below.                                                                                                DL-Admin DB Tasks 5
  14. 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.
  15. 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).

DL-Admin DB Tasks 6

 

 

Was this article helpful?

Related Articles