Learn the Simple Operation of Attaching and Detaching Databases
In this exercise, we are going to detach and then attach the Northwind database. This is actually a very simple task that can be performed with SQL Server. You can perform the steps presented here to move or copy a database.
Requirements:
As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment.
Setup:
None.
GUI Steps:
1) From Enterprise Manager we are going to detach the Northwind database. Right click on the Northwind database and select "All Tasks" - "Detach database".
2) This Detach Database screen pops up. Click on the "Update statistics prior to detach option". Then click the "OK" button. You will get a notification that the detaching of the database has completed successfully. Click "OK" to acknowledge the successful detach and return to Enterprise Manager.
3) Now if we return to Enterprise Manager, the Northwind database is no longer visible. (If it is still visible for you right click on "Databases" and choose "refresh").
4) We could copy these detached files to another server and attach them there if we wished (assuming that the server setups were the same), but we're not going to do that, we're just going to reattach them to the same database. In Enterprise Manager, right click on "Databases" and choose "All Tasks" then "Attach Database"
5) The "Attach Database" screen pops up. Hit the ellipses button to search for the .mdf (primary data file) of the database to attach. Once selected, all files belonging to the database will appear. In our case, the primary data file and the transaction log for the Northwind database are displayed. Choose the name of the database to attach as, specify the database owner, then click the "OK" button. You will receive a notification that the attaching of the database has gone successfully. Click OK to continue. If you go into Enterprise Manager like in step 3, you will now see that Northwind appears in the database list (if it doesn't right click on databases and choose "Refresh"). Our Detach/Attach operation is now complete.
T-SQL Steps:
1) In query analyzer, connect to the Northwind database and run sp_helpfile. This will give you the name and paths for all of the files that you will need to attach. Note that the full path isn't shown in the image below due to space constraints.
2) Connect to the master database in Query Analyzer and detach the Northwind database.
3) Note that the Northwind database is gone. You can't connect to it using the dropdown list of databases in Query Analyzer.
4) In query analyzer, run the sp_attach_db stored procedure. Specify a database name and all file locations to pull from. You will get a notification in the Query Analyzer results pane that the command has completed successfully. The Northwind database will once again appear in the dropdown list databases that are available for connection. The detach/attach operation is now complete.
