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:
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:
Each step should be considered in order. Depending on the recovery required it may be appropriate to skip some of these steps.
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.
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:
Only proceed with the next step (the restore of Windows 2000) when the backup utility can see the tape drive.
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:
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:
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:
then most probably you did not reset the restore options to always replace files. See step 2 above and repeat the restore.
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.
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:
Then delete all the files in the 'data' directory and try again. (You
may be able to get away with just deleting '
The MASTER database can only be restored when the database is in single-user mode. To place it in single user mode:
To restore the MASTER database from the backup use the following script (run in SQL Server Query Analyzer):
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:
the two fields to look at in the result set are the '
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:
Remember that the password for the 'sa' account will now be what it was at the time of the original backup.
Each of the databases can now be restored:
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.