Creating legacy backups of SQL Server databases

Edit online

You can create a legacy backup of your standard SQL Server databases by using Microsoft™ Management Console (MMC). You can also use the legacy method to back up availability databases with SQL Server 2012 and later versions.

Before you begin

To run a legacy backup, ensure that the Data Protection for SQL Server license file is installed.
On SQL Server 2012 and later versions, you can also back up availability databases in an AlwaysOn Availability Group (AAG) regardless of which availability replica is used for the backup operation. To back up availability databases, ensure that IBM Storage® Protect Snapshot is configured to use an AlwaysOn node. Additionally, specify the AlwaysOn node in the AlwaysOn Node field in the IBM Storage Protect Node Names page of the IBM Storage Protect Configuration Wizard.

About this task

When you change log and set backups to a new management class, the new management class setting takes effect only for new backups. Existing backups are not rebound to the new management class. Therefore, schedule a new full backup after you change the management class for either log or set backups.

Procedure

Start MMC.
Select the SQL Server instance in the tree view.

On the Protect tab of an SQL Server instance, select an option for viewing databases.

Table 1. Database backup views
Task	Action
View a list of SQL Server databases that are available for a backup operation	Click View: Databases.
View a list of SQL Server 2012 and later version availability databases that are available for a backup operation	Click Standard Databases. Information about the availability databases in an availability group is displayed, including the replica role, synchronization state, and space and log usage. Toggle the Standard Databases / Availability Databases button for the respective database views.

Refine the list of available databases in the results pane by entering a keyword in the Search field.

Verify the backup options. If the backup options are not displayed, click Show Backup Options.

Table 2. Database backup options
Option	Action
Data Stripes	Use this option to specify the number of data stripes to use in a backup or restore operation. The `numstripes` variable can be in the range 1 - 64. The default value is 1. When you use a multiple stripes number for legacy backups, and set the Verify Only parameter to Yes to restore the legacy backup, the number of stripes for the legacy restore must be equal to or greater than the number of stripes for the legacy backup.
Estimated Database % Change	Use this option to specify the estimated percentage of the database that changed since its last full database backup. The default value is 20. This estimate is useful because SQL Server does not provide a way to determine the size of a differential backup, and because the IBM Storage Protect server requires an accurate size estimate to efficiently allocate space and place objects. The IBM Storage Protect server uses this value to determine whether there is enough space in the primary storage pool to contain the backup.
Estimated Log % Change	Use this option to specify the estimated percentage of an SQL Server database that changed due to non-logged operations since the last log backup. The default value is 0.
Truncate Logs	Use this option to specify whether to dispose of entries that you no longer need in the SQL Server database transaction log after you back up the log. The default value is Yes. In general, you do not want to truncate the log when you rebuild a corrupted database. This option enables the server to back up the transaction log but does not affect the data. All transaction log entries are written from the time of the last log backup to the point of database corruption. If you do not truncate the transaction log, you might be able to back up the transaction log of a damaged, suspect, or unrecoverable SQL Server database.
Back Up Tail-Log	Use this option to store log records that are not backed up. By storing these records, also known as the tail of the log, the log chain is kept intact. Before you can recover an SQL Server database to the last point in time, you must back up the tail of the transaction log. The tail-log backup is the last backup of interest for the database recovery plan.
SQL Server Checksum	Use this option to verify the integrity of a legacy database backup. Integrity checking is a process that validates the values in a file or configuration for unexpected changes. Values are verified between the current state and the baseline state. In the Performance Properties window of MMC, you can enable or disable the checksum option for all your legacy databases at once. You can override the global setting, and temporarily enable or disable the checksum option for a database backup, by setting this SQL Checksum option to Yes or No.

In the Actions pane, click Backup Method and select Legacy.
In the Actions pane, select TSM for the Backup Destination.
The only option that is available to you is TSM because the database backups are stored on IBM Storage Protect server storage.
Optional: Choose a mode for the current task:
- Run Interactively: Click this item to run the current task interactively. This selection is the default.
- Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard runs the command that is required to complete the task.
To start the backup operation, in the Actions pane, take one of the following actions:
- Full Backup
- Copy-Only Full Backup
- Differential Backup to TSM
- Log Backup to TSM
Review the status of the backup operation by clicking Task List in the results pane. Click Task Details to view detailed status information.

What to do next

To determine which databases backups are bypassed during backup processing, review the tdpsql.log in the directory where Data Protection for SQL Server is installed. Data Protection for SQL Server bypasses database snapshots and databases that are in offline, mirroring, and restoring states.
To determine whether the checksum option is applied to a legacy database backup, issue the tdpsqlc query tsm * command on the command-line interface, or the equivalent Get-DpSqlBackup cmdlet.