Cry How to...

Restore a W2K system and SQL Server 2000 databases from a full backup

These notes are provided as is, for the purpose of assisting with the development of a procedure for the restoration of an Windows 2000 system and SQL Server 2000 databases. No guarantee is stated or implied. A backup cannot be relied upon until the restore has been tested and demonstrated successfully.

These notes are applicable only to Windows 2000 running SQL Server 2000. See also:

Pre-requisites

These notes assume that a backup has been performed to a single tape in accordance with the notes 'How to script a full Windows 2000 and SQL Server 2000 backup'. Those notes described a process to backup the Windows 2000 system and SQL Server 2000 databases to a single tape.
Do not approach any restore operation lightly. Be particularly careful restoring onto a working system, because a restore operation that goes wrong could wreck both the system and your day.
These notes assume a reasonable knowledge of Windows 2000.

Introduction

The notes on this page cover how to recover a Windows 2000 system and SQL Server 2000 databases following a catastrophic failure requiring everything to be restored. They assume that the steps covered in 'How to script a full Windows 2000 and SQL Server 2000 backup' have been followed to produce the backup that is to be restored from.

The restoration procedure described here has not been developed or tested for databases involved in replication. If the SQL Server database is involved in replication then it may be wise to read the notes on this page in conjunction with those in the note 'Restore an NT4 system and SQL Server 7 databases from a full backup', which has been tested with respect to restoring replicated SQL Server 7 databases.

Steps covered here:

Write protect the backup tape
Reload Windows 2000
Restore Windows 2000 from backup
Rebuild SQL Server 2000 MASTER database
Restore SQL Server 2000 MASTER database
Restore other SQL Server 2000 MODEL, MSDB and other databases

Each step should be considered in order. Depending on the recovery required it may be appropriate to skip some of these steps.

Write Protect The Backup Tape

It may seem trivial but it is vitally important that before you start any restore operation that you write-protect the tape containing the backup. This is normally done by sliding a tab over on the tape cartridge. This protects the backup against it being accidentally overwritten. Many horror stories would have been avoided if this simple precaution was taken.

Reload Windows 2000

In order to be able to recover from the backup Windows 2000 must be up and running. If Windows 2000 is already running then there is no need to perform this step. Only reload Windows 2000 if the system will not boot because the disk has been wiped or replaced.

Reloading Windows 2000 will require access to the original media, be this Windows 2000 Workstation or Windows 2000 Server. When reloading Windows 2000 it does not matter what name is given to the server, domain, network settings etc, because all of these will be recovered as part of the backup process.

If reloading onto a new disk, it is vital that you know the original disk partitioning information and drive letter assignments. The information is not recovered as part of the restore process. Exact disk partition sizes may not matter provided each partition is recreated large enough to hold the original data.

Be sure to load:

Windows 2000 itself
Any SCSI drivers (assuming the tape drive is a SCSI device)
Tape drivers (so that the tape can be accessed). These are loaded via Control Panel > Tape Devices.

Only proceed with the next step (the restore of Windows 2000) when the backup utility can see the tape drive.

Restore Windows 2000 From Backup

Warning: Be aware that a full restore should only be done onto a machine with the same hardware. Differences in some peripherals (e.g. different types of SCSI cards/drivers) may make the restored system unbootable on a different machine. If you find that the machine will not start (blue screen or hangs) after doing a restore then reload Windows 2000 and consider only restoring the individual files that you require. This does not affect SQL Server (although drives designations must be the same).

If the active directory service is running (it will not be if Windows 2000 has been freshly installed) then:

Reboot the server.
Press F8 when Windows 2000 starts to boot.
From the Windows 2000 Advanced Options menu select 'Directory Services Restore Mode'. This will start the server without active directory, and will boot into 'safe mode'.

The above can be ignored if the active directory has not yet been installed.

Note: If there are any drives other than the C drive then ensure that these have been partitioned and formatted.

To perform the restore:

Run the backup utility (Start > Accessories > System Tools > Backup)
Change the restore options to allow it to always replace files, this is under Tools > Options, select on 'Restore' tab and set the option 'Always replace the file on my computer'. (This allows it to replace an existing file with an older one from the tape - this is essential otherwise you will encounter problems when trying to restore SQL Server.)
Insert the tape. The presence of the tape should be recognised and a message window appear titled 'New Import Media'. (This may take a few minutes.)
Select 'Allocate this media to Backup' and click '[OK]'.
On the 'Restore' tab, highlight the type of drive folder (e.g. '4mm DDS') and the media should be listed. Right click the media and select 'catalog'.
Expand the tape folder and select the tape media just catalogued. Check each drive letter listed (which will be just C: if only C: was backed up) and check the 'system state'. (There will be SQL Server backup sets also listed and these can be ignored at this stage but if unsure check everything - the SQL server backup sets will be ignored during the restore at this stage.)
Click the 'Start Restore' button. There will be a warning if you are restoring the system state.
On the confirm dialog box click 'Advanced' and check all the options. (Optional)
After okaying the dialogs the restore will commence. When the restore is completed allow it to reboot.

When the restore is complete (and the system rebooted) all files, accounts, applications, names and other settings should be back to their original state at the time of the backup. The Windows 2000 component of the restore is now complete.

Note: If you get the following error from SQL Server when logging on after rebooting:

The ordinal 29 could not be located in the dynamic link library odbcbcp.dll

then most probably you did not reset the restore options to always replace files. See step 2 above and repeat the restore.

Rebuild SQL Server 2000 MASTER Database

The MASTER database only needs to be rebuilt if it is missing or totally corrupt. If the MASTER database exists then skip to the next step of restoring it. If restoring onto a clean system then this step is required.

Start a command session.
There is a bug in the version of 'rebuildm' which ships with SQL Server. (Rebuildm is the utility for rebuilding the master database. The bug is that it leaves the read-only file attribute set on the database files which means the rebuild fails - for further details see the Microsoft knowledge base article Q273572.) To work around this bug you will need to copy the contents of '<cd-drive>:\X86\Data' from the original Microsoft SQL Server 2000 cd to a directory on the server (it can be deleted once the rebuild process has been completed). Clear the read-only file property on each of these files.
Type:
rebuildm
The 'data' directory should point to the original location of the MASTER database and should not need changing (unless the database was originally installed or needs to be installed to a different location). The source directory should point to the directory into which the '<cd-drive>:\X86\Data' files were copied. Then click '[ Rebuild ]'.

This will reset the MASTER, MODEL and MSDB databases to their original status when SQL Server was first installed. Typically this will be with a blank password for the 'sa' account. You can now restart the server or manually restart the SQL Server service.

If the rebuild ends with the error:

Rebuild Master Failed with error: -1

The error occurred during server configuration. Refer to install\cnfgsvr.out and the log\errorlog files in C:\ Program Files\Microsoft SQL Server\MSSQL for diagnostic information.

Then delete all the files in the 'data' directory and try again. (You may be able to get away with just deleting 'master*', 'model*' and 'msdb*' but I have not tested this.) It is most likely that one of the files in the data directory was read-only or newer than the ones that rebuild needs to copy in.

Restore SQL Server 2000 MASTER Database

The MASTER database can only be restored when the database is in single-user mode. To place it in single user mode:

Start a command session.
To ensure that SQL Server is not running type:
net stop MSSQLServer
This may say that this will also stop another service, in which case allow it to continue. It will also indicate if the service is already stopped.
To start SQL Server in single user mode type:
sqlservr -m

If this is not recognised then cd to 'C:\Program Files\Microsoft SQL Server\MSSQL\BINN' and try again.

To restore the MASTER database from the backup use the following script (run in SQL Server Query Analyzer):

use MASTER go Restore Database MASTER From Tape='\\.\Tape0' With File=3, NOUNLOAD go

Note: While SQL Server is in single user mode you should still be able to connect from SQL Server Query Analyzer if you connect using the "sa" account.

The above assumes that the file number is 3 for the MASTER database. If unsure on the file number then the list of file numbers can be found by issuing the command:

Restore HeaderOnly from Tape='\\.\Tape0' with nounload

the two fields to look at in the result set are the 'BackupName' and 'Position' which is the file number to be used in the above.

Once the MASTER database has been restored (the restore will have caused the database to be shutdown) start the database by issuing the following command at the command line:

net start MSSQLServer

Remember that the password for the 'sa' account will now be what it was at the time of the original backup.

Restore Other SQL Server 2000 MODEL, MSDB and other databases

Each of the databases can now be restored:

Start SQL Server Query Analyzer (Start > Programs > Microsoft SQL Server > Query Analyzer)
Enter and run the following script:

use MASTER go Restore Database MODEL from Tape='\\.\Tape0' with File=4, NOUNLOAD Restore Database MSDB from Tape='\\.\Tape0' with File=5 Restore Database MYDB from Tape='\\.\Tape0' with File=N1 Restore Database YOURDB from Tape='\\.\Tape0' with File=N2 Restore Database ANODB from Tape='\\.\Tape0' with File=N3 go

Note:

Be sure to check that the file numbers are the correct for your system. (To determine the file numbers see the notes on restoring the MASTER database above.)
You must restore the MODEL and MSDB databases.
In the above replace the ficticious database names ('MYDB', 'YOURDB' and 'ANODB') with the real database names for the system. Also, be sure to check that the file numbers are the correct for your system.
The 'nounload' only needs to be specified once.

I would recommend completing the restore process by rebooting the server, this will ensure that any other processes which rely on the database should start normally.

These notes have been tested against SQL Server 2000 running under Windows 2000. These notes are provided as is, to assist with the development of backup and restore scripts and procedures. Be sure to test the backup and restore of any system. No guarantee stated or implied.

About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.