How to: Attach a Database File to SQL Server Express

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

You can attach a database file to an instance of SQL Server Code-Named “Denali” 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.

Security note Security 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]
    CREATE DATABASE [database_name] ON 
    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\<database name>.mdf' ),
    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\<database name>.ldf' )

    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
  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 Note

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