Brian Cryer

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:

Pre-requisites

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.

Backup Scripts

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 'FullNT4SqlBackup.cmd'. The first line ('ntbackup ...') 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:

backup That this is a backup, not a restore or eject.
/d "NT Complete Backup" Sets a brief description of the backup set.
/t normal Sets the type of backup.
/b Includes the registry in the backup.
/hc:on Use hardware compression.

The 'osql' statement runs a script that backs up the SQL Server 7 database. The '-P' flag specifies the password for the '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
GO

-- 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'
GO

The default behaviour is for SQL Server to eject the tape after it has backed up each database. This is overridden by the 'NOUNLOAD' option against the first database. To have the tape ejected at the end of the script, add ', UNLOAD' to the end of the last 'BACKUP ...' 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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: 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.