How to: Attach a Database File to SQL Server Express

  • Article

You can attach a database file to an instance of SQL Server 2005 Express Edition (SQL Server Express) by using the sqlcmd tool. For example, if you have existing database files from an old installation of SQL Server Express, you can attach these files to a new installation of SQL Server Express. Likewise, if you want to restore a corrupt database from a backed-up copy, you can attach the backed-up database file.

ms165673.security(en-US,SQL.90).gifSecurity Note:
We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

To Attach a Database File

  1. Open the command prompt on the server.

  2. From the command prompt, connect to an instance of SQL Server by using the following sqlcmd command:

    sqlcmd -S Server\Instance

    Where Server is the name of the computer and Instance is the name of the instance.

  3. When connected, type the following commands:

    USE [master]
GO
CREATE DATABASE [database_name] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<database name>.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<database name>.ldf' )
 FOR ATTACH ;
GO

    Where database_name is the name of the database you want to attach, FileName is the path and filename of the database file and the log file, and FOR ATTACH specifies that the database is created by attaching an existing set of operating system files.

  4. To verify that the database has been attached, type the following two commands:

    select name from sys.databases
go

  5. The sqlcmd tool displays the names of all databases attached to this instance of SQL Server Express. In the list, you should see the database name you provided in step 3.

Note

For more information on attaching a database, see Detaching and Attaching Databases, and CREATE DATABASE (Transact-SQL) in SQL Server 2005 Books Online.