Cry How to...
Script a full NT4 and SQL Server 7 backup
These notes are provided as is, for the purpose of assisting with the development of scripts to perform a full NT4 and SQL Server 7 backup. No guarantee is stated or implied. A backup system 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 restore an NT 4 system and SQL Server 7 databases from a full 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.
A backup requires a working tape device. It is possible to backup to another server across a network, but these notes assume a working tape drive. These notes also assume that the tape device has sufficient capacity.
To create a script that will perform a full backup of the NT 4 system together with the SQL Server 7 databases onto a single tape, first create a command file containing the following:
ntbackup backup c:\ /d "NT Complete Backup" /t normal /b /hc:on
osql -U sa -P "psswrd" -i SqlFullBackup.sql
This can be done with a text editor such as
notepad. Name this file
suitably, such as '
The first line ('
performs a backup of the NT 4 system. The line, as shown, assumes that only
the C drive needs to be backup up, add other drives as appropriate for your
system. Other switches on the line are:
That this is a backup, not a restore or eject.
/d "NT Complete Backup"
Sets a brief description of the backup set.
Sets the type of backup.
Includes the registry in the backup.
Use hardware compression.
osql' statement runs a script that backs up the SQL
Server 7 database. The '
-P' flag specifies the password for
sa' account, and this will need to be specified appropriately
for each site. Create the '
SqlFullBackup.sql' script using
notepad, use the following as a template:
-- This script will perform a full backup of all the SQL Server 7
-- databases, except TEMPDB. This is because TEMPDB
-- is recreated each time SQL Server starts.
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
BACKUP LOG MYDB WITH TRUNCATE_ONLY
BACKUP LOG YOURDB WITH TRUNCATE_ONLY
BACKUP LOG ANODB WITH TRUNCATE_ONLY
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO TAPE='\\.\TAPE0' WITH NAME='MASTER COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO TAPE='\\.\TAPE0' WITH NAME='MODEL COMPLETE'
BACKUP DATABASE MSDB TO TAPE='\\.\TAPE0' WITH NAME='MSDB COMPLETE'
BACKUP DATABASE MYDB TO TAPE='\\.\TAPE0' WITH NAME='MYDB COMPLETE'
BACKUP DATABASE YOURDB TO TAPE='\\.\TAPE0' WITH NAME='YOURDB COMPLETE'
BACKUP DATABASE ANODB TO TAPE='\\.\TAPE0' WITH NAME='ANODB COMPLETE'
The default behaviour is for SQL Server to eject the tape after it has
backed up each database. This is overridden by the '
option against the first database. To have the tape ejected at the end of
the script, add '
, UNLOAD' to the end of the last '
...' line, e.g.:
BACKUP DATABASE ANODB TO TAPE='\\.\TAPE0' WITH NAME='ANODB COMPLETE', UNLOAD
Putting it all together
The above scripts should be sufficient to perform a full backup of the NT 4 system together with all the SQL Server 7 databases. The only steps remaining to complete the task are:
- Review it.
The scripts presented here may be suitable for your site with only the disk drives and database names amended, or you may require something more sophisticated. Review the process to be confident with it. If things go wrong it will be YOU that bears the responsibility.
- Test it.
To be relied upon the backup script must be tested. Part of this testing must involve the restore process. The restore process is covered by a separate note here. Preferably the restore should be to a different machine (or the same machine with the original disks replaced) otherwise restoring to the same machine may hide problems that only come to light when you need to do a complete restore because of a catastrophic error such as disk failure or a fire.
- Automate the backup.
The simplest strategy is to have the task scheduler run the backup every night or once a week. All you then have to do is remember to change the tapes.
- Safe storage.
The backup tape produced is very important. It is your sole means of recovery. Store if safely - away from the server, preferably in a fire safe or off-site.
These notes have been tested against SQL Server 7 running under NT 4. These notes are provided as is, to assist with the development of backup scripts. 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.