Creating legacy backups of SQL databases

You can create a legacy backup of your standard SQL databases by using the 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 license file is installed.
On SQL Server 2012 and later versions, you can also back up availability databases in an AlwaysOn Availability Group regardless of which availability replica is used for the backup operation. To back up availability databases, ensure that Tivoli® Storage FlashCopy® Manager is configured to use an AlwaysOn node. Additionally, specify the AlwaysOn node in the AlwaysOn Node field in the TSM Node Names page of the Tivoli Storage Manager Configuration Wizard.

Procedure

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

On the Protect tab of the SQL instance, specify the type of SQL data to back up by browsing to and selecting the databases to back up.

Table 1. Database backup views
Option	Action
For a list of discovered SQL databases that are available to back up	Click View: Databases.
For a list of discovered SQL Server 2012 and later version availability databases that are available to back up:	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 using a multiple stripes number for legacy backups, and setting the Verify Only parameter to Yes to restore the legacy backup, the number of stripes for legacy restore should be equal or greater than the number of stripes for the legacy backup.
Estimated Database % Change	Use this option to specify the estimated fraction of the database that changed since its last full database backup. The default value is 20. This estimate is needed because SQL Server does not provide a way to determine the size of a differential backup, and because the requires an accurate size estimate to efficiently allocate space and place objects. The 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 fraction of an SQL database that changed due to non-logged operations since the last log backup. The default value is 0.
Truncate Logs	Use the option to specify whether to dispose of entries that you no longer need in the SQL 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 rebuilding a corrupted database. This option enables the server to back up the transaction log but does not affect the data in any way. 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 the option to store log records that are not yet 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 latest 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 the 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.

On the Actions pane, click Backup Method and select Legacy.
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 starts, and integrates the command that is required to complete the task.
On the Actions pane, click the backup type that meets your needs.
- 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 Tivoli Storage FlashCopy Manager is installed. Tivoli Storage FlashCopy Manager 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.