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:
- How to script a full NT 4 system and SQL Server 7 backup
- How to script a full Windows 2000 and SQL Server 2000 backup
- How to restore a Windows 2000 system and SQL Server 2000 databases from a backup
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 NT 4 system and SQL Server 7 backup'. Those notes described a process to backup the NT system and SQL Server 7 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 NT.
Introduction
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:
- Write protect the backup tape
- Reload NT 4
- Restore NT 4 from backup
- Rebuild SQL Server 7 MASTER database
- Restore SQL Server 7 DISTRIBUTION and Replicated Databases
- Restore SQL Server 7 MASTER database
- Restore SQL Server 7 MODEL, MSDB databases
- Restore other SQL Server 7 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 NT 4
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:
- NT 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 NT) when 'ntbackup'
can see the tape drive.
Restore NT 4 From Backup
- Load the backup tape (write-protected) into the tape drive.
- Start NT backup, either from the command line ('ntbackup') or from the Start menu (Start > Programs > Administrative Tools (Common) > Backup).
- In the tape window, select each drive listed (there will only be one if only the C drive was backed up).
- Click on the [ Restore ] button, and for the restore specify:
Restore to drive: C (i.e. to the original location) [/] Restore Local Registry [/] Restore File Permissions Logging options do not matter - Click [ OK ] to start the restore.
- If it asks to replace any files then select 'Yes to all'.
- It will need a reboot to complete the restore of some files.
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.
Rebuild SQL Server 7 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.
- Start a command session.
- Ensure that SQL Server is not running, the simplest means is to
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. - Type:
rebuildm - This will require the original SQL Server cd. (If it auto-runs then
close it down.) The 'data' directory should point to the original location
of the MASTER database and should not need changing. The source directory
should point to '
<cd-drive>:\X86\Data'. 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.
Restore SQL Server 7 DISTRIBUTION and Replicated Databases
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):
net start MSSQLServer
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'):
use MASTER
go
Restore Database DISTRIBUTION from Tape='\\.\Tape0' with File=N1, NOUNLOAD
Restore Database MYDB from Tape='\\.\Tape0' with File=N2
Restore Database YOURDB from Tape='\\.\Tape0' with File=N3
go
Note:
- Be sure to check that the file numbers are the correct for your
system. If unsure then the list of file numbers can be found by issuing
the command:
Restore HeaderOnly from Tape='\\.\Tape0' with nounloadthe 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. - The distribution database is called 'Distribution' by default, but it may be different on your system.
- The two other databases shown in this script are fictitious. Replace them with the names (and file numbers) of the real databases involved in replication for your system.
- If you are unsure whether a database is involved in replication then, to be safe, restore it at this point.
- There is a high likelihood that with transactional replication that the subscribers may hold a more recent update that will have been lost to the publisher. Merge replication seems to normally recover without any problem.
Restore SQL Server 7 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
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=2, 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 2 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 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 SQL Server 7 MODEL, MSDB Databases
After the MASTER database has been restored, the MODEL and MSDB databases must also be restored.
- Start SQL Server Query Analyzer (Start > Programs > Microsoft SQL Server 7.0 > Query Analyzer)
- Enter and run the following script:
use MASTER
go
Restore Database MODEL from Tape='\\.\Tape0' with File=3, NOUNLOAD
Restore Database MSDB from Tape='\\.\Tape0' with File=4
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.)
Restore Other SQL Server 7 Databases
Each of the remaining (application) databases can now be restored. The simplest procedure is to use a script such as the following:
use MASTER
go
Restore Database MYDB from Tape='\\.\Tape0' with File=N1, NOUNLOAD
Restore Database YOURDB from Tape='\\.\Tape0' with File=N2
Restore Database ANODB from Tape='\\.\Tape0' with File=N3
go
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.