Cry How to...
Restore an NT4 system and SQL Server 7 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 NT 4 system and SQL Server 7 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 NT 4 running SQL Server 7. See also:
The notes on this page cover how to recover the NT system and SQL Server 7 databases following a catastrophic failure requiring everything to be restored. They assume that the steps covered in 'How to script a full NT 4 system and SQL Server 7 backup' have been followed to produce the backup that is to be restored from.
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 NT must be up and running. If NT is already running then there is no need to perform this step. Only reload NT if the system will not boot because the disk has been wiped or replaced.
Reloading NT will require access to the original media, be this NT 4 Workstation or NT 4 Server. When reloading NT 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 NT) when '
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 NT 4 component of the restore is now complete.
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.
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.
If the database has a DISTRIBUTION database then that (together with the databases involved in replication) must be restored next. If the database is not involved in replication or is not a publisher then this step should be skipped.
Although it may be counter intuitive to restore the DISTRIBUTION and associated replicated databases at this point, I have yet to be able to successfully restore them except as the next step after rebuilding the MASTER database.
Before restoring the distribution database, be sure that the share used as the snapshot folder exists and has the necessary share permissions on it. If this was on a disk that has already been restored as part of the NT restore then this can be taken as read. If it is on a separate disk then be sure to recreate it. Generally, SQLServerAgent on the server requires full access and agents on remote servers require read only access.
SQL Server should be running in order to restore the distribution database. If unsure, issue the following command (in a command window):
The distribution database can only be restored if you have its exclusive use. Unfortunatly it cannot be set to single user if it is currently currupt or suspect. Check using SQL Server Enterprise Manager whether there are any users other than 'system' or 'sa'. If there are then disconnect them. (SQLServerAgent is a prime suspect here.)
Now enter and run the following script (use SQL Query Analyzer and connect as 'sa'):
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 2 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 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.
After the MASTER database has been restored, the MODEL and MSDB databases must also be restored.
Each of the remaining (application) databases can now be restored. The simplest procedure is to use a script such as the following:
In the above replace the fictitious database names with the real database names for the system. Also, be sure to check that the file numbers are the correct for your system.
For replicated databases only: Do not restore any databases that were restored at the same time as the DISTRIBUTION database.
I would recommend completing the restore process by rebooting the server, this will ensure that any other processes which rely on the database should be able to start.
These notes have been tested against SQL Server 7 running under NT 4 on a database using merge and transactional replication. 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.