By Brian Donahue

Because SQL Server locks the main database file, it is unavailable for a standard file backup. Although this lock protects your valuable database, it means that, after you run your file backup, you will need additional processes and resources to ensure the security of your database.

This article explores solutions for backing up your database and file system, with emphasis on consolidating these tasks.

Possible solutions

Tape backups produced by SQL Server agent jobs and file backups produced by the NTBackup program built into Windows are not compatible. That is, NTBackup can't restore a database from a file made by the SQL Server agent.

Your only recourse is to back up your regular file and then use a separate tape to back up your SQL Server database. This solution is straightforward, but it is not considered further in this article, because it is complicated to implement and may leave your database unprotected.

Other solutions that will be discussed in greater detail are:

1. Coordinating your file system and database backup.
2. Consolidating the SQL Server and file system backups with compression and encryption.

The first solution is the most common: You use the SQL Server agent to perform scheduled database backups to files on the hard disk, and later back up those files on tape as part of the regular file backup.

This requires a bit of coordination. If the database backups do not finish before the tape backups begin, for example, the tape backup will not contain all of the day's database backups. To solve the problem, you can run the database backups and file backups from the same batch job, effectively synchronizing your SQL Server and file backups.

NTBackup is an inexpensive and convenient solution for a single-server tape backup, but its automation interface is disappointing. When you schedule an NTBackup job, it merely creates a command-line syntax for you and passes it to the standard Windows scheduler. Automating backup jobs normally entails writing batch files that contain one or more NTBackup commands, and changing the behavior of the backup, depending on the date or the day of the week.

SQL database backup process

Let's first discuss how to perform SQL database backups before moving on to file backups.

SQL Backup, from Red Gate Software, provides a utility called SQLBACKUPC.exe that enables you to perform database backups from the command line, using SQL-like arguments. When the backups are complete, you can check the results. The following command produces a backup of the hypothetical Northwind database on the default SQL Server instance:

C:\Program Files\Red Gate\SQL Backup\SqlBackupC.exe" -SQL "BACKUP DATABASE [Northwind] TO DISK = 'c:\sqlbackups\<AUTO>' WITH NAME = 'Database (Northwind), 13/09/2005 18:12:35', DESCRIPTION = 'Backup on 13/09/2005 18:12:35 Database: Northwind Instance: (local) Server: BRIAN', ERASEFILES = 7, MAILTO = 'support@red-gate.com', COMPRESSION = 2" –E

Not only will this command back up the Northwind database to a location set in the SQL Backup settings, it will also email the backup report to the support person for review, compress the backup, and delete backups older than seven days.

Using the FOR command, available from scripting on the command prompt, we can perform similar operations on a group of directories. Using FOR, we can replace parts of the SQL Backup command with a variable, in this case the name of a directory on the hard disk.

We also enumerate the directories in the backup folder and substitute the directory name for the database name. This enables us to add databases to the backup job simply by creating a new subfolder in the backup folder with the same name as the database we want to back up.

		REM =====Set our basic backup command=====
	SET BACKUPCMD="c:\program files\red gate\sql backup\sqlbackupc.exe"
		REM =====Set location for our SQL Backups
	SET SQBROOT=c:\scripts\backup databases\backups
		REM =====Set a basic time string (Mon-2005-09-15) ===
	FOR /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do set dt=%%i-%%l-%%k-%%j
		REM =====Backup a database for every subfolder of our backup folder====
	CD %SQBROOT% FOR /D %%i IN ("*.*") DO %BACKUPCMD% -SQL "BACKUP DATABASE [%%i] TO DISK='%SQBROOT%\%%i\<AUTO>' WITH NAME='Full Backup %%i on %DT%', DESCRIPTION='Full Backup of %%i on %DT%', COMPRESSION=2, ERASEFILES=1" -E

File backup process

Now that we have written a script to back up our database to disk using SQL Backup, we can commit these backups to tape with NTBackup.

NTBackup enables us to write a backup description using the /d switch. This is useful because we want to create two backups on the tape: one database backup and one file backup of C:\, excluding the folder in which we have saved the SQL Backup files. When we specify a backup description, we make it easier for the operator who will perform the restore to recognize which backup set contains the files, and which contains SQL Backup database backups.

Because we will run two NTBackup commands in this script, we need to append the second backup to the tape so the first backup isn't overwritten. This is accomplished using the /A switch, which requires that we know the globally unique identifier, or GUID, of the current tape. When a tape is used for the first time, NTBackup labels it with this GUID so it can keep its removable storage database current.

To obtain the tape GUID, we need to query the removable storage service using the RSM command. RSM, or removable storage management, is independent of NTBackup, but it is the service responsible for tracking removable media usage on Windows systems

If we look at My Computer->Properties->Device Manager, we can see the tape drive listed. That's all the information we need to write a batch command to extract the GUID of the current tape, as in the following:

	REM ===== Get the tape drive name from Device Manager ===== SET TapeDriveName=Seagate STT20000A
	Rem Note that there is a tab after delims. FOR /F "usebackq delims= tokens=1,2" %%i IN (`rsm view /tlibrary /guiddisplay`) DO IF /i "%%i"=="%TapeDriveName%" SET TapeLibraryGuid=%%j
		REM =====Get the GUID from the tape=====
	FOR /F "usebackq" %%i IN (`rsm view /tphysical_media /cg%TapeLibraryGuid% /guiddisplay /b`) DO set tapeguid=%%i
		REM =====Get the partition for the tape====
	FOR /F "usebackq" %%i IN (`rsm view /tpartition /cg%tapeguid% /guiddisplay /b`) DO set partguid=%%i
		REM =====Get the logical media GUID====
	FOR /F "usebackq" %%i IN (`rsm view /tlogical_media /cg%partguid% /guiddisplay /b`) DO set logguid=%%i
		REM =====The logical GUID is not in the 8-4-4-4-12 that NTBACKUP needs
	set p1=%logguid:~0,8% set p2=%logguid:~8,4% set p3=%logguid:~12,4% set p4=%logguid:~16,4% set p5=%logguid:~20,12% set bkguid=%p1%-%p2%-%p3%-%p4%-%p5% Echo %bkguid%

Now the tape backup can be performed. NTBackup enables you to back up a specified directory or a saved selection of files to disk. We'll do both. With the first NTBackup command, we will back up the backups directory to tape and label it databases from <computername>.

The next backup saves the remaining files and directories from the C:\ drive to tape, using a saved selection of files. This selection can be created using the NTBackup interface or a text editor. For this demonstration, a selection of files called example.bks was saved. The contents of example.bks is as follows:

C:\ C:\scripts\backup databases\backups\ /Exclude

We can now include our NTBackup commands to commit the SQL Backup files and the remaining files to two backup sets on the same tape.

	START /WAIT C:\WINDOWS\system32\ntbackup.exe backup "%SQBROOT%" /v:no /r:no /rs:no /hc:on /m normal /d "Databases from %COMPUTERNAME% on %DT%" /j "Databases from %COMPUTERNAME% on %DT%" /l:s /um /G "%bkguid%"
		REM ===== Append a normal file backup next. REM ===== I use a saved selection file that doesn't REM ===== back up the SQL Backup folder. Note the /A argument for append
	START /WAIT C:\WINDOWS\system32\ntbackup.exe backup "@c:\scripts\backup databases\example.bks" /v:no /r:no /rs:no /hc:on /m normal /d " Normal file backup" /j "Normal File Backup" /l:s /A /G "%bkguid%"

When the backup is completed, we can see that our SQL Backup database backups and the normal file backup exist on two distinct sets on the same tape:

Conclusion

SQL Backup's command-line interface, SQLBACKUPC.exe, enables integration with batch commands that can be scheduled using the Windows scheduler service. Although SQL Server database backups normally use the SQL Server agent to schedule backup jobs, SQL Backup streamlines the tape backup processes to prevent information loss. It also makes restore procedures more straightforward by segregating SQL database backups from other files, so they can be more easily identified.

###

Brian Donahue is a support engineer and network administrator for Red Gate Software, and a Microsoft Certified Professional for Windows XP operating systems.

Additional information

Sample batch file

The following is a sample batch file for consolidating SQL Server and file backup tape using Windows backup (NTBackup.exe).

	@echo off
		REM ====== This batch job will do a full backup of all databases REM ====== named by creating a subfolder in %SQBROOT%
	REM ===== Get the tape drive name from Device Manager ===== SET TapeDriveName=Seagate STT20000A
		REM =====Set our basic backup command=====
	SET BACKUPCMD="c:\program files\red gate\sql backup\sqlbackupc.exe"
		REM =====Set location for our SQL Backups
	SET SQBROOT=c:\scripts\backup databases\backups
		REM =====Set a basic date string (Mon-2005-09-15) ===
	FOR /f "Tokens=1-4 Delims=/ " %%i in ('date /t') do set dt=%%i-%%l-%%k-%%j Rem Note that there is a tab after delims. FOR /F "usebackq delims= tokens=1,2" %%i IN (`rsm view /tlibrary /guiddisplay`) DO IF /i "%%i"=="%TapeDriveName%" SET TapeLibraryGuid=%%j
		REM =====Get the GUID from the tape=====
	FOR /F "usebackq" %%i IN (`rsm view /tphysical_media /cg%TapeLibraryGuid% /guiddisplay /b`) DO set tapeguid=%%i
		REM =====Get the partition for the tape====
	FOR /F "usebackq" %%i IN (`rsm view /tpartition /cg%tapeguid% /guiddisplay /b`) DO set partguid=%%i
		REM =====Get the logical media GUID====
	FOR /F "usebackq" %%i IN (`rsm view /tlogical_media /cg%partguid% /guiddisplay /b`) DO set logguid=%%i
		REM ===== Parse the GUID and return it in the proper format as %bkguid%
	set p1=%logguid:~0,8% set p2=%logguid:~8,4% set p3=%logguid:~12,4% set p4=%logguid:~16,4% set p5=%logguid:~20,12% set bkguid=%p1%-%p2%-%p3%-%p4%-%p5%
		REM =====Backup a database for every subfolder of our backup folder====
	CD "%SQBROOT%" FOR /D %%i IN ("*.*") DO %BACKUPCMD% -SQL "BACKUP DATABASE [%%i] TO DISK='%SQBROOT%\%%i\<AUTO>' WITH NAME='Full Backup %%i on %DT%', DESCRIPTION='Full Backup of %%i on %DT%', COMPRESSION=2, ERASEFILES=1" -E
		REM =====Backup onto tape.
	START /WAIT C:\WINDOWS\system32\ntbackup.exe backup "%SQBROOT%" /v:no /r:no /rs:no /hc:on /m normal /d "Databases from %COMPUTERNAME% on %DT%" /j "Databases from %COMPUTERNAME% on %DT%" /l:s /um /G "%bkguid%"
		REM ===== Append a normal file backup next. REM ===== I use a saved selection file that doesn't REM ===== back up the SQL Backup folder. Note the /A argument for append
	START /WAIT C:\WINDOWS\system32\ntbackup.exe backup "@c:\scripts\backup databases\example.bks" /v:no /r:no /rs:no /hc:on /m normal /d "Normal file backup" /j "Normal File Backup" /l:s /A /G "%bkguid%"