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:
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
-- 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',
BACKUP DATABASE MODEL TO TAPE='\\.\TAPE0' WITH NAME='MODEL COMPLETE'
BACKUP DATABASE MSDB TO TAPE='\\.\TAPE0' WITH NAME='MSDB
BACKUP DATABASE MYDB TO TAPE='\\.\TAPE0' WITH NAME='MYDB
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
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
- 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.
As well as using the built-in facilities (above), there are a number
of third party backup and restore solutions available. Whilst not
exhaustive, you could consider the following:
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.