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.
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.
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:
|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.
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.