In the following illustration, I will use AdventureWorks.mdf as an example.
Execute the following statement to attach your MDF on SQL Management Studio:
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH_REBUILD_LOG
GO
If everything works fine, you may get the similar message below and have your database attached after execution:
-
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.LDF' was created.
-
File activation failure. The physical file name "C:\Users\<SomeUserName>\Documents\MyCode\Test\App_Data\AdventureWorks_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Depending on how or where you get your MDF file (because your MDF may not be detached properly), your LDF rebuilt may fail and so may your database attach command. In this case, you need to download and use the free tool SQL Server Express Utility (SSEUtil) to interact with the server and create the LDF file for you. This tool also works well with the full version of SQL Server 2005. There are two (2) big steps you need to perform in order to make your MDF finally available on SQL Management Studio.
Step 1: Use SSEUtil to attach MDF into the database server
Then at the command prompt, run sseutil's attach database command. Before doing it, please be sure to add your path to sseutil.exe. Otherwise, you need to use its absolute path name,., e.g., c:\util\sseutil.exe.
- sseutil -s .\SQLExpress -a "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdverureWorks
- -s to specify the instance of SQL server.
- -a to attach the database by specifying its MDF file path name.
- The last parameter is optional. It is the name of database you want to call (e.g., AdvantureWorks).
or
You can use sseutil console to interact with the server.
- To start a sseutil console, at the command prompt, type
sseutil -c
- You will see
Console mode. Type 'help' for more information.
1> - To attach a MDF, type
!attach "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdventureWorks - Type quit when you're done. This step is necessary to enable you to add the database into the sysdabases in the next step by removing the lock on file.
Step 2: Add MDF into sysdatabases
-
USE master
GO
EXEC SP_ATTACH_DB
@filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',
@dbname=N'AdventureWorks
or
-
USE master
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH
GO