INF: Database Device File Image Backup and sp_dboption |
Q134788
The information in this article applies to:
-
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:
- This method will not make a file image copy of master, model, or
tempdb, and all of these databases must be online.
- 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:
- Relationships among sysdatabases, sysusages, sysdevices.
- The DISK REINIT and DISK REFIT statements.
- Relationship between the syslogins table in the master
database and the user database sysusers table.
- Each database exists on its own database devices. Device
fragments must not be shared between databases.
- 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.
- 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.
- 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