Cry How to...
Script a full W2K and SQL Server 2000 backup
These notes are provided as is, for the purpose of assisting with the
development of scripts to perform a full Windows 2000 and SQL Server 2000
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 Windows 2000 running SQL Server 2000.
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 systemstate c:\ /p "4mm DDS" /d "W2K Complete
Backup" /um /hc:on
osql -E -i SqlFullBackup.sql
This can be done with a text editor such as
this file suitably, such as '
FullW2K4SqlBackup.cmd'. The first
ntbackup ...') performs a backup of the Windows 2000
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
|That this is a backup, not an eject.
|Include a backup of the system state.
|Backup the C drive. Add 'D:\' to also back up the D drive
and so on.
/d "W2K Complete Backup"
|Sets a brief description of the backup set.
|The media pool. This assumes that the tape backup is a 4mm
DDS drive, this may need to be changed for your system.
|Use the first available media. You may wish to review the
use of this flag for your system.
|Use hardware compression.
osql' statement runs a script that backs up the SQL
Server 2000 database. The '
-E' flag specifies that a trusted
connection is to be used. (Alternately use something like '-U sa -P "psswrd"'
to specify that the 'sa' account is to be used with the password given -
this will need to be specified appropriately for each site. You will need
to specify the server name (-S server) if the database is not running on
the local machine.) 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
Windows 2000 system together with all the SQL Server 2000 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 2000 running
under Windows 2000. 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.