SQL Server 2005 Introduction to SQL Server Management Studio - Exercise 2: Attaching a Database

(Page 3 of 6 )

Scenario

In this exercise, you will attach the AdventureWorks database using the “Attach Database” option in the SQL Server Management Studio Object Explorer.

Attaching a database means making all the database files available to your SQL Server, just as if you had created the database on the current server. Detaching a database allows you to move the physical files and reattach those files from a new physical location, or to make a copy of the files to use on another server (perhaps a test or development server).

Because the primary file contains the locations of all the other files in a database, if all the files are in their original location (as stored in the primary file) you only need to specify the primary file when attaching the database. SQL Server will read the information in the primary file and attach all the associated files for the database.

However, if the files are not all in the original location, the information stored in the primary file may not be sufficient for SQL Server to find and attach all the database files. You will then need to specify the exact physical location of each database file that is not in the location it was when the database was detached.

When a database is detached, SQL Server will do a checkpoint in the database, so all the committed transactions are written to the disk files.

            TASKS                       DETAILED STEPS

1. If the database is already attached, detach it.
    
   
   1. In the Object Explorer, expand localhost or the name of your server (if it's not already expanded), then expand the Databases folders.
      
   2. If the AdventureWorks database is in the list, right-click on it. Otherwise, skip to the Attach the AdventureWorks database task below.
      
   3. Point to Tasks, and then click Detach.
      
   4. In the Detach Database dialog box, click OK. You may have to double click the title bar to see the OK button.
      
2. Attach the AdventureWorks database.
    
   
   1. In the Object Explorer, right-click on the Databases folder, and click Attach.
      
   2. In the Attach Database(s) dialog box, click Add.
      
   3. Locate and select the following master file for the AdventureWorks database (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf), and click OK.
      
      Note that the Attach Database dialog box is not modal. This means that you can go back to the Management Studio and look for other information you might require while filling in the dialog box.
      
   4. Verify that there are two files listed in the AdventureWorks database details section in the bottom half of the Attach Database(s) dialog box.
      
   5. Click OK to attach the database. You may have to double click the title bar to see the OK button.
      
3. Use the Object Explorer to Verify the AdventureWorks Database.
   
   
   1. Collapse and expand the Databases folder in the Object Explorer.
      
   2. If the AdventureWorks database doesn’t appear, right-click the Databases folder and select Refresh.
      
   3. Expand the AdventureWorks database.
      
   4. Expand the Tables folder, and verify that there are several dozen tables.
      
   5. In the Solution Explorer, open the Queries folder under Exercise 2, and double click the file DatabaseProperties.sql.
      
      USE AdventureWorks
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'Recovery')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'Updateability')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoCreateStatistics')
      SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoShrink')
      
   6. Press F5 or click the Execute button on the toolbar to execute the batch. If requested, respond to the Connect to SQL Server dialog box.
      
   7. Examine the values returned for the various database properties.

Next: Exercise 3: Exploring an Existing Database >>
 

More MS SQL Server Articles
More By MSDN Virtual Labs

Comments On: SQL Server 2005 Introduction to SQL Server Management Studio

· We hope you found this article from MSDN Virtual Labs to be enjoyable and...    · Where are the projects for the SQL labs?Select C:\SQL Labs\Lab...    · Access Virtual Labs online at microsoft.com -> Learning -> Virtual Labs on MSDN   >>> Post your comment now!