INF: Database Device File Image Backup and sp_dboption

• Microsoft SQL Server, version 6.0

SUMMARY

One of the advantages of using Microsoft SQL Server version 6.0 is the variety of methods that can be used to perform a database backup. This article explores a variation of the file image backup.

There are two tape formats used on the Windows NT platform, and they are incompatible with each other. The first is the tape format supported by Windows NT, NTBACKUP.EXE, and other third party tape archiving products which use the Microsoft Tape Format (MTF). The second is the SQL Server tape format. In some scenarios, customers have elected to have an operator change tapes in between NTBackup's backup operation, and those database dumps to tape performed by SQL Server. Other customers have chosen to have SQL Server dump to disk, and then use NTBackup to archive this dump to tape.

There are two basic backup strategies for SQL Server. The primary backup strategy for SQL Server is the database dump which can then be loaded in a recovery scenario. This method is used primarily by companies whose servers must be in operation 7 days a week and 24 hours a day. Many of these companies also use the hot backup server strategy in which the dump is loaded to the backup server. These SQL Server database dumps are frequently performed to disk locally, to a network drive, and to tape.

The second basic SQL Server backup strategy is the use of a file image backup, where SQL Server is shutdown and the all the database device files are archived to tape using NTBackup. Because this method uses the NET STOP/NET START SQL Server commands, most customers use this method by combining batch scripts which are setup using the AT command and run by the Windows NT Scheduler Service.

MORE INFORMATION

With the release of SQL Server version 6.0, you can perform some of the same operations without stopping SQL Server and select which databases and the associated device files are archived to tape. Before going further, there are several precautions that must be stated:

1. This method will not make a file image copy of master, model, or tempdb, and all of these databases must be online.

2. File image backups of SQL Server's database device files are safely performed when all are archived as a set. This method of selectively archiving database device files requires an understanding of the following:

   a) Relationships among sysdatabases, sysusages, sysdevices. b) The DISK REINIT and DISK REFIT statements. c) Relationship between the syslogins table in the master

         database and the user database sysusers table.
   

   d) Each database exists on its own database devices. Device

         fragments must not be shared between databases.
   

3. The file image backup will only preserve the state of the database at the time when sp_dboption is executed. Up to the minute recovery can only be achieved through the use of database dumps and transaction log dumps.

4. You should perform database checks on the Master database (DBCC CHECKDB and DBCC NEWALLOC) and periodic database dumps of the master database. You should also use the MASTER.SQL script found in the Knowledge Base article "INF: Recovering a Full Master Database" (article number Q102077) which can be used to extract to a text file important configuration information about the installation of SQL Server.

5. If the file image backup method is used on a database that is currently using database and transaction log dumps as a backup method, a database dump needs to be performed after the database is brought online. The sp_dboption system stored procedure writes a checkpoint record when bringing the database online which may invalidate previous database/transaction log dumps.

Here is an example which uses a 4 MB database called 'test' which has two 2MB devices, one called 'testdata,' located on C:\SQL60\DATA\TESTDATA.DAT, and the second called 'testlog,' located on C:\SQL60\DATA\TESTLOG.DAT. The database options on test are TRUNCATE LOG ON CHECKPOINT and SELECT/INTO BULK COPY.

Step 1

   sp_dboption test,offline,true
   go

   Database is now offline
   Closing device 'testdata' and marking it 'deferred'.
   Device option set.
   Closing device 'testlog' and marking it 'deferred'.
   Device option set.

   select name,dbid,status,mode
   from master..sysdatabases
   where dbid = db_name('test')

 name                           dbid   status mode
 ------------------------------ ------ ------ ------
 test                                6    524      0

   Msg 5160, Level 16, State 1:
   Cannot take 'test' offline because usecount=1.
   Msg 15245, Level 16, State 1:
   'DBCC DBCONTROL' error.  Database not placed offline.

Step 2

Now execute the extended stored procedure to either copy the associated database device files from share to share, or, as in this example, execute NTBackup:

   xp_cmdshell 'ntbackup backup c:\sql60\data /a /v
   /d "Image Backup of Test Database" /t Incremental /tape:0'

Step 3

When the above command completes, bring the database back online with the following command:

   sp_dboption test,offline,false
   go

   Opening device 'testdata' and marking it as non-deferred.
   Device option set.
   Opening device 'testlog' and marking it as non-deferred.
   Device option set.
   Recovering database 'test'
   Database is now online.

   Opening device 'testdata' and marking it as non-deferred.
   Device option set.
   Opening device 'testlog' and marking it as non-deferred.
   Msg 5153, Level 16, State 1:
   Failed to open device 'testlog', see errorlog for details.
   Msg 15246, Level 16, State 1:
   Cannot bring database online due to problem opening device 'testlog'.