After you complete the configuration process, start the Management Console to protect your SQL Server data.
About this task
To start the Management Console, click Start > All Programs > Data Protection for Microsoft SQL Server> DP for SQL Management Console. If you try to use the Management Console before you complete the configuration process, the software does not function correctly.
The Management Console that is displayed is the Microsoft Management Console (MMC), with Data Protection for Microsoft SQL Server software displayed as a plug-in. The console uses a navigation tree to organize the computer data that you have registered. Each computer icon that is followed by the word Dashboard represents a physical computer.
When you register a computer, information about this particular system is collected and stored. Password information is encrypted and stored separately. The
computers that are registered are tracked with a globally unique identifier (GUID).
The GUID is assigned to each system and is used when backing up and restoring data.
You can create groups of computers. These groups consolidate information when you view the dashboard, prepare reports, and run group commands. By default, the computers in a group are selected when you complete tasks for the group, but you can select additional computers in the tree to include in an operation.
Determining managed storage capacity
You can track the capacity of managed storage assets during license renewal.
About this task
Typically there is a difference between the capacity that is used by server data and the capacity of the volume that contains that data. For example, a set of databases might require a capacity of 1 GB and reside on a 10 GB volume. When a snapshot of the volume is performed, the Data Protection for SQL Server managed capacity measurement is 10 GB.
Procedure
To determine managed storage capacity:
1. From the GUI, select an SQL instance.
2. On the Protect tab, click Properties in the Action pane.
3. Select Managed Capacity from the list of available property pages. The managed capacity is calculated and displayed.
4. View a list of the volumes (that contain backups) and their respective managed capacity, by clicking Show Details.
5. Close this window.
Using the Task Manager pane
The Task Manager provides a centralized panel in the Management Console (MMC) GUI from which to view, stop, remove, or manage backup, restore, and automation tasks.
About this task
When running backup, restore, or automation tasks, use the Task Manager pane.
Procedure
1. Start the Management Console.
2. Click the appropriate Protect Data or Recover Data task for your data in the welcome page of the MMC GUI.
3. Click Show Activity in the Action pane. The Task Manager panel opens beneath the results pane.
4. Choose a view for the current task:
v Task List (default): Click this item to view the following information about your operations:
Name State Result Progress Start Time Duration Messages
Use the Task List view to complete these tasks:
– Click Up and Down to modify the processing order for incomplete operations. Hover the cursor on the selected operation to view the command-line input.
– Click Stop to end an operation that is still processing. When an operation cannot be stopped, this button is not available.
– Click Remove to remove a completed or a scheduled operation.
– Copy the selected operation by either clicking the copy icon or
right-clicking a task and selecting Copy. Then you can run this command in the Automate tab or from a command prompt.
– Click the calendar icon to use the scheduler wizard to set up a schedule.
– Click the appropriate icon to view statistics or a performance chart for the selected operation.
v Task Details: Click this item to view the operation information (available in the Task List) in detailed format. Click Mode: Navigate and use the arrows to view details about each operation. Summary and error information is also available (when applicable).
Backing up SQL databases and files
Back up SQL databases and files with Data Protection for SQL Server.
Security
Data Protection for SQL Server requires certain settings in order to perform operations in a secure environment.
Windows administrator authority is required for installation. Data Protection for SQL Server must be registered to the Tivoli Storage Manager server and the appropriate node name and password must be used when connecting to the Tivoli Storage Manager server. In addition, standard Tivoli Storage Manager security requirements apply to Data Protection for SQL Server.
Three options are provided when specifying SQL Server logon information:
v Accept the default sa account and blank password.
v Use SQL user ID security and specify both the SQL user name and password.
With SQL user ID security, the SQL Server administrator provides the logon ID and the password that provides access to the SQL Server.
v Use a trusted connection and let Windows authenticate the logon.
Note: The SQL logon user or Windows user name must be added to the SQL Server SYSADMIN fixed server role before it can be used by Data Protection for SQL Server.
Backing up SQL databases by using VSS
You can back up SQL server data by using Microsoft Volume Shadow Copy Service (VSS).
Before you begin
You can also back up availability databases in an AlwaysOn Availability Group on SQL Server 2012.
Before you begin, see “Security” for the settings to use in a secure environment.
If you want to manage local VSS backups or run offloaded backups to Tivoli Storage Manager server storage, you must have Tivoli Storage FlashCopy Manager configured in your environment. If you make VSS backups to the Tivoli Storage Manager server, the Tivoli Storage FlashCopy Manager is not required.
Data Protection for SQL Server uses a single AlwaysOn node to back up availability databases in an AlwaysOn Availability Group regardless of which availability replica is used for the backup operation. Ensure that you configured Data Protection for SQL Server to use an AlwaysOn node. You also must specify the AlwaysOn node in the AlwaysOn Node field in the TSM Node Names page of the Tivoli Storage Manager Configuration Wizard. If you change the AlwaysOn node name field in the AlwaysOn Node properties page for your SQL workload, you must run the Tivoli Storage Manager Configuration Wizard to complete the reconfiguration of the name. If you do not want to use the Tivoli Storage Manager Configuration Wizard to register the node on the Tivoli Storage Manager server, you can use the Tivoli Storage Manager register node command.
Procedure
To back up standard SQL databases or availability databases by using the VSS method:
1. Start the Management Console (MMC) GUI.
2. When configured for use with a Tivoli Storage Manager server, if you plan to use offloaded backups, make sure a Remote DSMAGENT Node name is specified. An offloaded backup uses another system (specified with the Remote DSMAGENT Node nameparameter) to move SQL data to Tivoli Storage Manager server storage. Offloaded backups can reduce the load on network, I/O, and processor resources during backup processing.
To verify or specify a remote Remote DSMAGENT Node name, select the SQL Serverinstance in the tree view, and click Properties in the Actions pane. Then, select the VSS Backup property page. If the Remote DSMAGENT Node name is blank, enter a node name.
3. On the Protect tab of the SQL instance, specify the type of SQL data to back up:
v Click View: Databases for a list of discovered SQL databases that are available for backup.
v For SQL Server 2012: The Standard Databases / Availability Databases button toggles between the standard database view and the availability database view. The label on the button reflects the type of databases that are displayed in the view. To display a list of availability databases, 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.
Use the Protect tab to browse and select the databases to back up. Fine-tune the list of available databases in the results pane by entering a keyword in the Searchfield.
4. Verify the backup options. If the backup options are not currently displayed, click Show Backup Options.
v If you want to use offloaded backups, select True in the Offload field. This field applies only to VSS backups.
5. In the Actions pane, click Backup Method and select VSS.
6. In the Actions pane, for the Backup Destination, the only option is TSM. The database backups are stored on Tivoli Storage Manager server storage.
7. Optional: Choose a mode for the current task:
v Run Interactively: Click this item to run the current task interactively. This selection is the default.
v Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard starts up, complete with the command that is required to complete the task.
8. Create the backup by clicking Full Backup in the Actions pane. You can also right-click a database, and select the backup action that you want from the menu.
If you want to create a copy-only full backup, click Copy-Only Full Backup in the Actions pane. A copy-only full backup is independent of the sequence of SQL Server backups. A copy-only full backup is not used as a base for a differential backup. The copy-only full backup does not disturb the sequence for a differential backup. The differential backup would not be associated to the copy-full backup, but would be associated to the prior full backup that was
run. This type of backup can be used for special purpose backups that do not affect backup and restore procedures. It can be used for longer term retention than conventional backups.
What to do next
You can view the status of the backup operation by clicking Task List in the bottom half of the results pane. Click Task Details to view detailed status information.
Backing up SQL databases by using the legacy method
You can back up SQL databases by using the legacy backup method in the Management Console (MMC) GUI.
Before you begin
You can also use the legacy method to back up availability databases in an AlwaysOn Availability Group on SQL Server 2012.
There are several types of backup available for databases:
Full Backs up all of a database plus part of the transaction log.
Copy-Only Full
A type of backup that is independent of the sequence of conventional SQL Server backups. Transaction logs are not truncated with this backup. This type of backup can be used for special purpose backups that do not affect the backup and restore procedures, and can be used for longer term retention than conventional backups.
Differential
Backs up only the parts of a database that changed since the last full backup plus part of the transaction log.
Log Backs up the transaction log only, with or without truncation.
Before you begin, see “Security” on page 85 for the settings to use in a secure environment.
Data Protection for SQL Server uses a single AlwaysOn node name to back up availability databases regardless of which availability replica is used for the backup operation. Ensure that you configured Data Protection for SQL Server to use an AlwaysOn node name. You can set up the AlwaysOn node name in the AlwaysOn Node field in the TSM Node Names page of the Tivoli Storage Manager
Configuration Wizard.
About this task
Follow these steps to run a legacy backup of your standard SQL databases or availability databases:
Procedure
1. Start the Management Console (MMC GUI).
2. Select the SQL Server instance in the tree.
3. On the Protect tab for the SQL instance, ensure that the Databases view is selected.
4. For SQL Server 2012: The Standard Databases / Availability Databases button toggles between the standard database view and the availability database view.
The label on the button reflects the type of databases that are displayed in the view. To display a list of availability databases, 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.
5. Select one or more databases to back up.
6. Verify the backup options. If the backup options are not displayed, click Show Backup Options.
v Use the Stripes option to specify the number of data stripes to use in a backup or restore operation. The numstripes variable can range from 1 to 64.
The default value is 1.
v Use the DiffEstimate option to specify the estimated fraction of the database that changed since its last full database backup. This estimate is needed because SQL Server does not provide a way to determine the size of a differential backup, and because the Tivoli Storage Manager server requires an accurate size estimate to efficiently allocate space and place objects. The Tivoli Storage Manager server uses this value to determine whether there is enough space in the primary storage pool to contain the backup. The default value is 20.
v Use the LogEstimate option to specify the estimated the fraction of a SQL database that changed due to non-logged operations since the last log backup. The default value is 0.
v Use the Truncate option to specify whether to dispose of entries you no longer need in the SQL database transaction log after you back up the log. 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 try to touch the data in any way. It writes all transaction log entries 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.
The default value is True.
v Use the Backup tail of the log option to store log records that have not yet been backed up. By storing these records, also known as the tail of the log, the log chain is kept intact. Before you can recover a 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.
7. In the Actions pane, click Backup Method and select Legacy. TSM is the only available backup destination.
8. Optional: Choose a mode for the current task:
v Run Interactively: Click this item to run the current task interactively. This selection is the default.
v Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard starts up, complete with the command that is required to complete the task.
9. Create the backup by clicking one of these actions in the Actions pane:
v Full Backup
v Copy-Only Full Backup v Differential Backup to TSM v Log Backup to TSM
You can also right-click to select a database; then, from the context menu, select the backup action.
What to do next
You can view the status of the backup operation by clicking Task List in the bottom half of the results pane. Click Task Details to view detailed status information.
Backing up SQL groups or files by using the legacy method
You can complete a legacy back up of SQL groups or files by using the Management Console (MMC) GUI.
Before you begin
You can also use the legacy method to back up groups of files in availability databases in the SQL Server 2012 environment.
The following three types of backup are supported.
Group Backs up the contents of the specified file group.
File Backs up the contents of the specified logical file.
Set Backs up the contents of the specified groups and files.
Attention: You must back up the transaction logs after completing a Group, File, or Set backup operation.
Data Protection for SQL Server uses a single AlwaysOn node name to back up availability databases regardless of which availability replica is used for the backup operation. Ensure that you configured Data Protection for SQL Server to use an AlwaysOn node name. You can set up the AlwaysOn node name in the AlwaysOn Node field in the TSM Node Names page of the Tivoli Storage Manager
Configuration Wizard.
About this task
Follow these steps to run a Legacy backup of SQL groups or files in a standard SQL database or availability database:
Procedure
1. Start the Management Console.
2. Select the SQL Server instance in the tree.
3. On the Protect tab for the SQL instance, make sure that the Files view is selected.
4. For SQL Server 2012: The Standard Databases / Availability Databases button toggles between the standard database view and the availability database view.
The label on the button reflects the type of databases that are displayed in the view. To display a list of availability databases, 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.
5. Select one or more groups or files to back up.
6. Verify the backup options. If the backup options are not displayed, click Show Backup Options.
v Use the Stripes option to specify the number of data stripes to use in a backup or restore operation. The numstripes variable can range from 1 to 64.
The default value is 1.
7. Optional: Choose a mode for the current task:
v Run Interactively: Click this item to run the current task interactively. This selection is the default.
v Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard starts up, complete with the command that is required to complete the task.
8. Create the backup by clicking one of the following Actions:
v Group Backup to TSM v File Backup to TSM v Set Backup to TSM
What to do next
You can view the status of the backup operation by clicking Task List in the bottom half of the results pane. Click Task Details to view detailed status information.
Restoring SQL databases and files
Restore SQL databases and files with Data Protection for SQL Server.
VSS restore considerations
When you run VSS restores, refer to the following list of considerations.
Unless otherwise specified, VSS restores refers to all restore types that use VSS (VSS restore, VSS fast restore, VSS instant restore).
v A VSS instant restore operation overwrites the entire contents of the source volumes. However, you can avoid overwriting the source volumes by specifying InstantRestoreFalse in the Management Console (MMC) GUI. This option bypasses volume-level copy and uses file-level copy instead to restore the files from a VSS backup that is on local shadow volumes. The source volume must
v A VSS instant restore operation overwrites the entire contents of the source volumes. However, you can avoid overwriting the source volumes by specifying InstantRestoreFalse in the Management Console (MMC) GUI. This option bypasses volume-level copy and uses file-level copy instead to restore the files from a VSS backup that is on local shadow volumes. The source volume must