You can use the attach database function of SQL Server to attach the MDF file and create the database.
This can be done one of two ways:
1. Using Enterprise Manager
- Expand the Databases tree
- Right click and select All Tasks
- Select Attach Database
- Find and select the MDF file
- The GUI will display the name of the MDF and LDF files and there should be an X on the LDF file name
- Change the name of the database and owner as needed and select OK
- An error message will appear that says "Filename specified for log is incorrect. Do you want to continue", click on Yes
- The database should now be attached to your server. If the database came from another server you will probably need to take care of user and login mismatches.
EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
Click here for a similar tip posted March 28 2005.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in August 2005