INF: Database Device File Image Backup and sp_dboption

ID: Q134788

The information in this article applies to:

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:

    1. Relationships among sysdatabases, sysusages, sysdevices.


    2. The DISK REINIT and DISK REFIT statements.


    3. Relationship between the syslogins table in the master database and the user database sysusers table.


    4. 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.


The cornerstone of this technique is the use of sp_dboption which has an additional option to take a database offline. As a side effect of using this stored procedure, the database has its status updated so that it is unavailable, the associated database devices are closed, and their status in sysdevices is updated to 'deferred' or 8192.

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

Issue the command: 

   sp_dboption test,offline,true
   go

Which results in the following messages returned to the client: 

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

This command resets the status for the 'test' database, and the value in sysdatabase.status will be 512 plus the status at the time the stored procedure was executed. Verify this with the following command: 

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

which results in the following: 

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

The stored procedure checks to ensure that there are no users currently using the database; if there are, the following message is returned:
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'

The basis for the above command is that when NTBackup performs a 'NORMAL' backup, the archive attribute for a file is cleared until it is subsequently changed. If all the archive bits are cleared for a set of database device files, when a database is taken offline the database device files will be marked with +A or the archive bit set on. An incremental backup will locate these files, back them up, and reset the archive bit to an off position. The files in the directories specified with the archive bit set on will tend to be those most recently closed, and this status bit set. (See the NTBackup Help file for details regarding the command line execution of NTBACKUP.EXE, or Windows NT Help Commands for additional information on the Attrib command)

Step 3

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

   sp_dboption test,offline,false
   go

which returns the following statements: 

   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.

If there is a problem opening one of the devices, the following errors are displayed: 

   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'.

The selected database should now be online and accessible.

Additional query words: sql6 methods 


Keywords          : kbenv kbusage SSrvAdmin SSrvProg 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 15, 1999