Detaching and Attaching a Database

Another way to deploy your databases is by distributing the physical database and log files. Microsoft has chosen this route of deployment when you install SQL Server or when you rebuild the master database.

When installing SQL Server, the master database and log files are copied into the data directory and attached to the server. If you try to copy or move a file for a SQL Server that is started, you receive a sharing violation.

This section deals with workarounds for those sharing violations, including how to reattach a database and its log files back to the server after deployment. You can deploy a database with this method by following these steps:

  1. Detach the database.

  2. Copy or move the files to the new location.

  3. Attach the data and log files to the new location.

  4. You may need to fix the login to user mapping if moving to a new server.

Once a database is attached to the new server, it is an exact copy of the database that was copied. This includes any user permissions that accompany the database. Attaching a database is convenient because it also creates the database for you, and it is very quick since the data and log space has already been allocated.

Detaching a Database

Before you can distribute your database and log files in their native format, you have to detach your database from the server. Detaching a database essentially removes any remnants of the database from the SQL Server, and frees the file to be moved or copied. You can do this in Enterprise Manager by right-clicking the database you'd like to detach and selecting All Tasks | Detach Database. This brings up the Detach Database screen shown in Figure 8-7.

click to expand
Figure 8-7: Detaching a database in Enterprise Manager

Before you can detach the database, you must ensure that all users have disconnected from the database. You can do this either by running the usp_Killusers stored procedure I mentioned earlier in this chapter from Query Analyzer or by clicking the Clear button. At that point, you are given the signal that the database is ready to detach. If any users are connected to the database while you try to take it offline, you receive the following error:

Server: Msg 3701, Level 16, State 3, Line 1 Cannot detach the database 'NewNorthwind' because it is currently in use.

You can also select Update Statistics Prior To Detach to perform that work before you detach the database. This saves you time when you attach the database, since SQL Server will have up-to-date statistics on the database. With the database detached, you can copy the files to a different server or attach to the same server.

Caution 

Once you detach a database, the database is gone. The files still exist and are easily reattached.

You can also detach a database in T-SQL by using the sp_detach_db system stored procedure. (In SQL Server 7.0, this is the only method of detaching a database.) To use the stored procedure, simply specify the database and indicate whether you'd like to skip the UPDATE STATISTICS statement. If you use the value ‘false' for the last parameter, UPDATE STATISTICS is run against each table in the database.

sp_detach_db 'SQLServerDB', 'false'

This command outputs the following results to the client:

Running UPDATE STATISTICS on all tables Updating dbo.Articles Updating dbo.Categories Updating dbo.Category_Articles Updating dbo.dtproperties   Statistics for all tables have been updated.

Other Methods of Copying Data Files

I generally find that administrators want to keep the database intact and not detach it from the server. As I mentioned before, if you try to copy the physical data and log files, you receive a sharing violation from Windows, because SQL Server is keeping the database open (unless you have the Auto Close option selected in Database Options). To make SQL Server release the file, you can stop SQL Server or take the database offline. To take the database offline, you can use the ALTER DATABASE syntax as shown here:

ALTER DATABASE Northwind SET OFFLINE

Once the database is offline, you are free to copy or move the files. To reopen the database for users, use the following syntax:

ALTER DATABASE Northwind SET MULTI_USER

Attaching a Database

Now that you have the data files detached, you're ready to attach them to the new location. To attach the database in Enterprise Manager, right-click Databases and select All Tasks | Attach Database. In the Attach Database screen (see Figure 8-8), specify the first database file. Once you select the first database file, SQL Server ensures that it can find the original files. If it can't find all of the original files, a red X is placed next to the filename. You have to correct the path and filename under the Current File(s) Location column. Once you have corrected the information, choose a database name for the Attach As option, and specify the owner of the database under the Specify Database Owner option. And don't forget that when you attach a database from a different server you must fix the users, as discussed earlier in this chapter.

click to expand
Figure 8-8: Attaching a database in Enterprise Manager

Note 

The database will inherit any of the database properties of the original file. For example, if a database was set to single user mode, it will be set to single user mode when it is attached.

You can also attach a database by using T-SQL through the sp_attach_db system stored procedure. The stored procedure only requires two parameters: the new database name and the data and log filenames. You can attach up to 16 files with this method. Notice I said 'new database name.' Once you copy the file to the new location, you can name the database whatever you want. This means you can create a database once and use it as a template to create many databases.

The sp_attach_db command uses the following syntax:

sp_attach_db @dbname = N'NorthwindNew', @filename1 =  'C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwindnew.mdf', @filename2 =  'C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwindnew.ldf'

Uninstalling SQL Server or Upgrading from an Evaluation Edition

Detaching and attaching databases is really handy when you upgrade from an evaluation copy of SQL Server. After your evaluation of SQL Server expires (typically 120 days), the SQL Server services will not start. The painless workaround is to follow these steps:

  1. Stop SQL Server.

  2. Make a copy of your data and log files (these shouldn't be needed, but as a precaution I always perform this step).

  3. Fully uninstall the evaluation or beta version of SQL Server. Leave the SQL Server directory structure and files intact.

  4. Install the licensed copy of SQL Server.

  5. Attach the databases you had installed previously either from the backup you created earlier or from the \data directory.

After you remove the trial version of SQL Server, the data and log files are retained. This is by design so you can perform this function to save your databases.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net