How to Back up an MS SQL Server Using the Tivoli Storage Manager Backup/Archive Client

Technote (FAQ)


Question

Although it is possible to backup MS SQL Server using the Tivoli Storage Manager Backup-Archive Client, there are limitations which preclude it from being a recommended solution. The reasons for this, and possible alternatives, are described below.

Cause

While the MS SQL Server is running, it is continually writing to the MDF files. This means that those files are always open, preventing other applications from accessing the files. For this reason, when an MS SQL Server is installed, it tells the Windows OS to exclude the SQL directories from backups, since neither Tivoli Storage Manager nor Windows 'ntbackup' can directly read these files. Instead, the data is accessible through the MS SQL Server API.

Answer

Here are techniques to backup the MS SQL Server using Tivoli Storage Manager:

1) Supported and recommended: Using "Tivoli Storage Manager for Databases" product
"Data Protection for MS SQL Server" (Tivoli Data Protection/SQL).

2) Supported: Within MS SQL Server, perform a native backup to disk; then use the Tivoli Storage Manager B/A Client to back up the resulting backup flat file(s) to the Tivoli Storage Manager Server.

3) Not recommended: Halt the MS SQL Server, then attempt to directly back up the database files using the Tivoli Storage Manager B/A Client.

4) Not Supported and Not recommended: Attempting to directly back up a running MS SQL Server's database files using the Tivoli Storage Manager B/A Client.

The simplest and safest technique is to use the "Tivoli Storage Manager for Databases" product "Data Protection for MS SQL Server" (Tivoli Data Protection for SQL), which directly interfaces with the SQL API to back up the data to the Tivoli Storage Manager Server through the Tivoli Storage Manager Client API. Tivoli Data Protection for SQL is a separate product with individual licensing.

Another supported technique is using a combination of manual actions and/or user-written Windows schedules and scripts from within the MS SQL Server Administration, to back up the databases to disk; then use the Tivoli Storage Manager Client to backup those MS SQL ".BAK" files. Assuming the scripts wait for a sufficient time to allow the MS SQL backup to complete, it may be possible to run them from a PRESCHEDULECMD option in the Tivoli Storage Manager Client schedule. The Tivoli Storage Manager Backup/Archive Client only needs access to the directory (such as "SQLBACKUP") with the MS SQL ".BAK" files; it can leave the actual running MSSQL directories and files untouched. In this case, the MS SQL Server can remain running while the "flat files" are being backed up.

During the MS SQL Server installation, it tells the Windows operating system "you cannot back up my directories"; so it is typically not possible to use the Tivoli Storage Manager B/A Client to directly back up the MSSQL directories and files. As a work-around, you could attempt to override the OS-level EXCLUDE statements; for example using a SELECTIVE backup syntax that explicitly specifies the MSSQL data directories. Before the backup begins, you would need to halt the MS SQL Server so the files are not open/locked, and wait a sufficient time for the SQL Server to completely stop. However, since Microsoft Support does not recognize this technique as a valid backup/restore configuration, any problems with the MS SQL Server after restoring would be unsupported.

Attempting to use the Tivoli Storage Manager B/A Client to back up a running instance of MS SQL Server is not supported and will not work because the data files are open, changing, and locked by the Windows operating system. You can attempt to back up the open/locked MS SQL Server files using products such as "St Bernard Open File Manager" or the Tivoli Storage Manager B/A Client feature "Open File Support" (OFS); which uses Tivoli Storage Manager "Logical Volume Snapshot Agent" (LVSA). However, there is no guarantee that any OFS feature or product would allow a complete backup of the MS SQL Server data allowing a restore with database integrity. Neither IBM/Tivoli nor Microsoft offer support for restoring a MS SQL Server backup using this configuration.

For more information about Tivoli Data Protection for SQL, here are links to the product website and documentation:
http://www-306.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManagerforDatabases.html

Here are the supported Tivoli Data Protection for SQL platforms:
http://www-01.ibm.com/support/docview.wss?uid=swg21218747

To download the "Tivoli Storage Manager Data Protection for Microsoft SQL Server Installation and User's Guide" please start at this website and select product version, then "PDF format for printing"
http://www-01.ibm.com/support/knowledgecenter/SSTFZR/welcome

Document information


More support for:

Tivoli Storage Manager for Databases
Data Protection for MS SQL

Software version:

All Versions, Version Independent

Operating system(s):

Windows 2003 server, Windows 64bit

Software edition:

All Editions, Edition Independent

Reference #:

1293954

Modified date:

2009-09-24

Translate my page

Content navigation