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. See also:

• How to script a full NT4 and SQL Server 7 backup
• How to restore an NT 4 system and SQL Server 7 databases from a full backup.
• How to restore a Windows 2000 system and SQL Server 2000 databases from a backup.

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 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 notepad. Name this file suitably, such as 'FullW2K4SqlBackup.cmd'. The first line ('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 line are:

backup	That this is a backup, not an eject.
systemstate	Include a backup of the system state.
c:\	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.
/p "4mmDDS"	The media pool. This assumes that the tape backup is a 4mm DDS drive, this may need to be changed for your system.
/um	Use the first available media. You may wish to review the use of this flag for your system.
/hc:on	Use hardware compression.

The '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 2000
-- 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 2000 system together with all the SQL Server 2000 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 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.