The detach and attach is one of the procedure to change, move SQL database to a different instance of SQL Server. The SQL database changes can be perform locally or between server. This is alternative method from backup, restore procedure.
Detaching Steps
The detach steps will remove the database from the MS SQL Server instance. Before you proceed, please ensure below items are checked,
- System database cannot be detach.
- Remove database replication, disable database publishing if enabled.
- Drop database snapshot, if it is exists on the database
- No database mirrored session are running
Step 1: Launch the SQL Studio, right click to the database, select Tasks and Detach.
Step 2: Select below options and click OK.
- Drop Connections
- To drop any existing connection to the database.
- Update Statistics
- To update optimization statistics to the database.
Step 3: The data and log files will remain in the same location. You can now move them to the target location.
Attaching Steps
Step 1: On the target location, right click to the Databases and select Attach.
Step 2: Specify the MDF file location. Verify and adjust the MDF and LDF location accordingly. Click OK.
Done!
Note: This attach and detach steps can be used if you want to relocate or move files to another locations.
