Moving Database using detach and attach -- Sqlserver DBA Basics

If you need moving the database to another location, the attach and detach functionality is indispensable. SQL Server is a database management system used to store data and objects needed to work with that data. 

SQL Server works by allowing the user to specify a location on the physical drives accessible by the server and providing the engine needed to store, retrieve, organize and utilize the data and objects within the database. 

The database is stored in files on hard drives that are accessed in order to gain access to the contents of the database. The files associated with a database have a default location, but upon database creation, they can be configured to go to another location. 

This allows for data to be better configured and organized across hard drives that may have limited space available for data storage. 

This is where the concept of moving the database by attaching and detaching comes into play.

When a database is used and managed by SQL Server, the data files on the hard drives associated with that database are locked to use by any other application. 

If you try to access that file, a message will alert you to the fact that the file is being used by another application and you cannot access it. This makes the possibility of moving a database from one location to another or copying the database for use by another agency out of the question. 

For example, if you were travelling to another location to work on an application and you had to work from a machine that did not have access to that database, you could take a copy of the database to use to work with. 

This is where the ability to attach and detach a database will come into play. You can detach a database in order to remove it from the control of SQL Server and enable the files to be accessed by external methods or applications. 

This is where the concept of detaching a database can be used. By detaching a database it can be copied and moved to another location without interference from the SQL Server managing that database. 

Detaching a database in Enterprise Manager can be accomplished by the following:

• Right-click on the database in question. This will bring up menu options shown below:
  
  
   
  
  
  
  
  After bringing up the menu options, go to the Detach Database option under the All Tasks menu option as illustrated below:
  
  
   
  
  
  Select the Detach Database option to begin detaching the database from the database management system. After selecting the Detach Database option the following screen will appear to confirm the detach operation: 
  
  
   
  
  
  This screen informs the user of the number of active connections to the database.
  
  
  This can be helpful so that users of the database can be informed that the database they are using is getting ready to become unavailable so that they can save the work that they are working on help you determine a better schedule for the when the detachment operation can occur. 
  
  
  The screen also shows whether or not the database is being used in replication and whether the database is ready to be detached. 
  
  
  If you would like to update the statistics for the database prior to detaching it, the option can be selected. 
  
  
  To proceed with the detachment of the database click the OK button. To cancel the operation, click the Cancel button. The detaching of the database is confirmed with the following dialog box: 
  
  
   
  
  
  The database can also be detached from the database management system using Query Analyzer and the Transact-SQL language. To do so, open a session in Query Analyzer and enter the sp_detach_db command. 
  
  
  The syntax for the command is as follows: 
  
  
  sp_detach_db 'dbname' ,'skipchecks' ] 
  
  
  The dbname is the database name that is used by SQL Server to access the database. The skipchecks parameter is optional and instructs the server to either update the statistics for the database or not. 
  
  
  For example, if we wanted to detach the Northwind database that comes with SQL Server the following statement would accomplish that feat: 
  
  
  sp_detach_db 'Northwind’ 
  
  
  Once the database is detached you can start moving the database to another location or transport it to the location where it is needed and can be attached to the server in order to access the database once again in SQL Server. 
  
  
  To access the database once again via the database management system, it must be reattached to the database. 
  
  
  In order to perform the attachment of a database via Enterprise Manager, right click on the databases tab in Enterprise Manager, select the All Tasks option and then the Attach Database option from the menu. 
  
  
  The following illustration shows the options needed to perform the operation:
  
  
    
  
  
   By selecting the Attach Database command the following dialog box is shown to continue the operation. 
  
  
   
  
  
  
  The first parameter that must be input is the mdf file for the database that needs to be attached. The command button shown to the left will bring up the file browser to allow the standard file selection methods to be employed.
  After the file is selected, it can be verified using the Verify command button. A name is given to attach the database as and also to specify the owner of the database.
  The OK command button will finalize the attach operation and shows the following dialog box to alert the user that the operation was successful. 
  
  
   
    
  Clicking the cancel button can be used to cancel the operation from continuing.
  The database can also be attached by opening up a Query Analyzer session and using Transact-SQL to perform the operation.
  
  
  The syntax of the attach command is as follows:
  sp_attach_db 'dbname' ,'filename_n' 
  
  
  The dbname is the database name that is used by SQL Server to access the database. The filename_n indicates that there can be more than one file associated with the database. 
  
  
  There can be filename1 through filename16 files associated with the database. Once it is attached the database can be accessed by SQL Server again. 
  
  
  For example, in order to attach the Northwind database we detached in the previous example, the following statement can be used:
  
  
  sp_attach_db 'Northwind' ,'Path of .mdf', ‘path of .log’ 
  
  
  Many people may be asking themselves why they may need this ability, but for those of us who have had to send a database somewhere else for it to be used, it is a blessing that it exists.