Data Protection for SQL Server backup methods

Two types of backup are available: legacy backup and VSS backup.

Legacy backup

A legacy backup is a specialized API backup that functions with the SQL Server storage engine, as show in Figure 1-7. This is the type of backup provided by previous releases of Data Protection for SQL.

Figure 1-7 Data Protection for SQL legacy backup communications

A legacy backup creates a copy of all or part of an SQL database onto Tivoli Storage

Manager storage media. Data Protection for SQL Server provides selection mechanisms and the logic that are required to backup and restore SQL data.

When you initiate a legacy backup operation, Data Protection for SQL executes the following steps:

1. Starts a session with the server using the API IBM Tivoli Storage Managerand information contained in a client options file.

2. Starts a sessions with the SQL Server using internal Microsoft interface, known as SQL-DMO (Distributed Management Objects).

3. Instructs the SQL Server using the Microsoft SQL-VDI (Virtual Device Interface) to begin a backup of the selected database objects.

4. Receives data from the SQL Server and sends it to the Tivoli Storage Manager server.

5. Ends the TSM Server and SQL Server sessions.

The following characteristics are true of Legacy backup:

򐂰 Full, copy, incremental, differential, anda database copy types are supported

򐂰 Backup granularity is at the database level

򐂰 Backups are stored on IBM Tivoli Storage Manager server storage

򐂰 Backups are managed through IBM Tivoli Storage Manager server policy

򐂰 Backups can be performed in a Microsoft Cluster Server (MSCS) environment

򐂰 Backups provide SQL Server database integrity check funcionality

Note: When a backup is performed, Data Protection for SQL Server records information about the SQL Server and database. This information is available for query and restore operations. The information about the names and sizes of the database file groups and files is stored along with the database data, as sub-object. This sub-object is referred to as meta data. You will need ths sub-object when you need information about individual database file groups and files. SQL Database and Data Protection for SQL

Tivoli Storage Manager

Server

VSS backup

A VSS backup uses Microsoft Volume Shadow Copy Service technology to produce an online snapshot (point-in-time consistent copy) of SQL data that can be stored on local shadow volumes or on Tivoli Storage Manager server storage. A VSS backup means the SQL server is not in “backup mode” for an extended period of time because the length of time to perform the snapshot is usually measured in seconds and not hours. In addition, a VSS backup allows a snapshot of large amounts of data at the same time since the snapshot works at the volume level. VSS backups requires IBM Tivoli Storage Manager for Copy Services to be installed, in addition to Data Protection for SQL and the IBM Tivoli Storage Manager backup/archive client. You can optionally have an alternate machine to move the data to the IBM Tivoli Storage Manager server, an off-loaded backup.

Figure 1-8 General VSS architecture

Optionally, VSS backups can be stored locally on VSS shadow volumes that are directly accessible by the SQL system, as long as sufficient space is available for the snapshot. Local VSS shadow backups are fast because data is not transferred to Tivoli Storage Manager server storage. Restoring these backups is also fast because the SQL data is not transferred from Tivoli Storage Manager server storage over the network.

After the snapshot is complete, it can be accessed directly through standard operating system functions or other applications. One application of this is to back up and manage the VSS snapshot on an external storage manager (for example, IBM Tivoli Storage Manager).

When this function is selected, then after the snapshot is made, IBM Tivoli Storage Manager is automatically invoked to store the snapshot. In this way, we can establish two uses for VSS backup: as a backup in itself, so that multiple shadow copy generations can potencially be kept online for rapid restore (backup to local), and as means for extra backups to an external media (backup to IBM Tivoli Storage Manager), so that the database can be restored even if the disk system containing the original database and the shadow copies fails. When a Data

MIcrosoft

Primary application server Offload Offload

backup server

Protection for SQL backup is started, you must specify the backup destination: to local, IBM Tivoli Storage Manager, or both.

The following characteristics are true of VSS backup:

򐂰 Full VSS snapshot backups and full VSS off-loaded snapshot backups are supported.

Incremental, differential, and transaction log backup types are not supported. (see 1.6.2,

“SQL Server backup types” on page 21 for definitions of the types of backups)

򐂰 Backup granularity is at the database level only.

򐂰 Backups are managed through Tivoli Storage Manager policy.

򐂰 Backups can be stored on local shadow volumes, Tivoli Storage Manager server storage or both locations.

򐂰 Different policy settings can be defined for each storage location and backup method.

򐂰 Backups to Tivoli Storage Manager server storage can be off-loaded to an alternate machine, to reduce the workload on the production servers.

򐂰 Backups can be performed in a Microsoft Cluster Server (MSCS) or VERITAS Cluster Server (VCS) environment.