Problem solve Get help with specific problems with your technologies, process and projects.

Restoring SQL Server databases from .mdf files

Learn how to restore SQL Server databases from .mdf files and vaild backups tp prevent loosing your data by reinstalling and restarting your SQL server or updating system tables.

It does not take a rocket scientist to understand the importance of backups, especially considering how critical data is to most companies. There are many fine reference materials available about restoring SQL Server data from valid backups. But what if you don't have a valid backup? Then what?

Before I answer this question, let me explain how you might not have a valid backup. This scenario, which happened to one of my colleagues, is fairly common among database consultants. You show up at the client site one morning and the local network folks have upgraded your development server from Windows NT to Windows 2000 without letting you know. Oops! Now, all the work you've done with your databases is "gone with the wind." Suppose that you did not have any backups since you believed that your workstation was secure -- not a very smart choice but let's face it, we're all human. However, the network folks inform you that they backed up your data directory before wiping your machine. You cannot use the sp_attachdb system stored procedure to attach these files to your new SQL Server installation. Fortunately, there is a way to restore your SQL Server database. The following two methods will reveal the steps you need to take.

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
( NAME = northwind_dat,
   FILENAME = 'd:Program FilesMicrosoft SQL ServerDatanorthwind.mdf',
   SIZE = 10,
   MAXSIZE = 50,
( NAME = northwind_log,
   FILENAME = 'd:Program FilesMicrosoft SQL ServerDatanorthwind.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,

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'

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:

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'

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.

In this article I showed you two ways of restoring a database from an .mdf file, in case you do not have a valid backup. I hope you never have the situation like the one my colleague encountered. However, if you do, use either of the methods described to recover your databases.

About the Author
Baya Pavliashvili is a MCSE, MCSD and MCDBA with three years of experience with SQL Server.

This was last published in May 2002

Dig Deeper on Database Security Management-Enterprise Data Protection