Restoring SQL Server databases from .mdf files

Method One

@ Reinstall SQL Server and create a database with the same physical file name as the .mdf file that you have. For example, if you have northwind.mdf file, create a database with the following statement:

USE master
GO
CREATE DATABASE northwind
ON 
( NAME = northwind_dat,
   FILENAME = 'd:Program FilesMicrosoft SQL ServerDatanorthwind.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = northwind_log,
   FILENAME = 'd:Program FilesMicrosoft SQL ServerDatanorthwind.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

After ensuring that database was successfully created, stop your SQL Server service. Copy the .mdf file you have from the current directory to the SQL Server data directory. When asked whether you wish to overwrite the existing file, choose "yes." Now, start your SQL Server again. SQL Server will recover all the data and schema you had in the original database.

Method Two

This method is used when you don't want to stop and restart SQL Server. WARNING: be extremely careful when running the following code. Updating system tables with incorrect values might cause your server to crash!
Copy your .mdf file and paste it to SQL Server data directory. Next, configure your server to allow changes to the system tables. This can be accomplished by running the following code in the Query Analyzer:

sp_configure 'allow_updates', '1'
RECONFIGURE WITH OVERRIDE

Now, add an entry for your database to the sysdatabases system table in the master database. First, a quick lesson about sysdatabases table is in order. This system table tracks all user-defined and system databases that reside on the server. The columns that you should be concerned with are described in the following table:

COLUMN NAME	MEANING
Name	Database name
Dbid	Internal identifier for each database
Sid	Security id for the database - hexadecimal value
Mode	Used internally - do not set this value to anything other than 0
Status	Status bits that inform SQL Server of the database configuration options. Some of these options can be set using sp_dboption system stored procedure.
Status2	Additional status information, also in bit format
Reserved	Reserved for future use. Contains '1/1/1900' for all databases except model.
Crdate	The date the database was created
Category	Bitmap of the replication option used with the database
Cmptlevel	The database compatibility level; with SQL Server 2000, this could be 65, 70 or 80
Filename	The physical path to where the file is stored

The following code snippet shows you the statement that can be used to populate the sysdatabases table with an entry for the new database:

INSERT master..sysdatabases (name, dbid, sid, mode, status, status2, 
       crdate, reserved, category, cmptlevel, filename)
VALUES ('northwind', 10, 0x01, 0, 24, 1090519040, getdate(), '1/1/1900', 
        0, 80, 'd:Program FilesMicrosoft SQL ServerDatanorthwind.mdf')

Of course, if dbid of 10 is already taken, please choose another available identifier. You should examine sysdatabases table before adding any records to it. Now, reset your server to disallow updates to system tables:

sp_configure 'allow_updates', '0'
RECONFIGURE

Your database is now ready to be used. Again, please be extremely careful while making changes to sysdatabases system table (or any other system table for that matter). Inexperienced SQL Server users should use Method One.