Frequently asked questions

Edit online

Review the answers to the following frequently asked questions about Data Protection for SQL Server.

How can I compress my Data Protection for SQL Server backups?
You can use the following methods to compress your Data Protection for SQL Server backups:
  • Use the compression option to instruct the IBM Storage® Protect API to compress data before sending it to the IBM Storage Protect server. Compression reduces traffic and storage requirements.

    Where you specify the compression option depends on the backup method that you are using:

    • For legacy backups, specify the compression option in the Data Protection for SQL Server options file.
    • For VSS backups, specify the compression option in the backup-archive client options file that is used as the Local DSMAGENT Node. If the environment is configured for VSS offloaded backups, you must also specify the compression option in the backup-archive client options file that is used as the Remote DSMAGENT Node. Review the compression information available in the client documentation before attempting to compress your data.

    For more information about the compression option, see Specifying configuration parameters for IBM Storage Protect.

  • You can specify SQL Server backup compression from the SQL Properties windows in Microsoft™ Management Console (MMC), or you can use the sqlcompression option from the command line to set SQL Server native backup compression for Data Protection for SQL Server backups. For more information, see Enabling SQL Server backup compression.

    Backup compression is only available with legacy backups. You can run backup compression only on Enterprise Edition. You can run SQL Server 2008 R2 backup compression on Standard, Enterprise, and Datacenter editions. Any edition can restore a compressed backup.

How do I encrypt my Data Protection for SQL Server backups?
Use the enableclientencryptkey and encryptiontype options to encrypt Microsoft SQL Server databases during backup and restore processing.

Where you specify these options depends on the backup method that you are using:

  • For legacy backups, specify these options in the Data Protection for SQL Server options file.
  • For VSS backups, specify the encryption options in the backup-archive client options file that is used as the Local DSMAGENT Node. If the environment is configured for VSS offloaded backups, you must also specify the encryption options in the backup-archive client options file that is used as the Remote DSMAGENT Node. Review the encryption information available in the client documentation before attempting to encrypt your databases.

For more information about the enableclientencryptkey and encryptiontype options, see Specifying configuration parameters for IBM Storage Protect.

How do I deduplicate my Data Protection for SQL Server backups?
Use the deduplication option to enable client-side data deduplication. Client-side data deduplication is used by the IBM Storage Protect API to remove redundant data during backup processing before the data is transferred to the IBM Storage Protect server.
Where you specify these options depends on the backup method that you are using:
  • For legacy backups, specify the deduplication encryption options in the Data Protection for SQL Server options file.
  • For VSS backups, specify the deduplication option in the backup-archive client options file that is used as the Local DSMAGENT Node. If the environment is configured for VSS offloaded backups, you must also specify the deduplication option in the backup-archive client options file that is used as the Remote DSMAGENT Node. Review the deduplication information available in the client documentation before attempting to encrypt your databases.

For more information about the deduplication option, see Specifying configuration parameters for IBM Storage Protect.

Can I restore an individual table from an SQL Server backup?
Yes, but only for legacy backups. You cannot restore an individual table from a VSS backup. To restore an individual table from a legacy SQL Server backup, place the tables that require individual restore granularity into their own filegroup. Then, use Data Protection for SQL Server to restore a single filegroup from a full backup.
Can I restore an SQL Server database backup to an alternate SQL Server machine or instance?
Yes. You can restore legacy backups and VSS backups directly to an alternate SQL Server machine or instance.
Can I restore VSS backups to alternate locations within the same SQL Server instance?
You can restore VSS backups to alternate locations within the same SQL Server instance as follows:
  • Use the /relocatedir parameter from the command line.
  • Use the Relocate option in the Restore Databases window from the GUI.
Can I restore VSS backups to alternate database names within the same SQL Server instance?
You can restore VSS backups to alternate database names as follows:
  • Use the /into parameter from the command line.
  • Use the Restore Into option in the Restore Databases window from the GUI.
Can I use Data Protection for SQL Server to back up SQL Server databases, logs, and then also shrink the transaction log file?
Modify the command file that is used for scheduled backups with an entry that calls a T-SQL command file that shrinks the transaction log file. For example, in the following command file that is used for scheduled backups:
tdpsqlc backup * full
tdpsqlc backup * log
osql -E -i shrinkjob.sql
The file shrinkjob.sql is a T-SQL command file that shrinks the transaction log file.
Should I create a separate node name in order to create an archive backup of an SQL Server database?
First, use the same node name as the primary SQL Server node but add an extension for the archive node. For example:
Primary: SQLSRV550_SQL
Archive: SQLSRV550_SQL_ARCH
Second, use a separate Data Protection for SQL Server options file (dsmarchive.opt) that contains the archive node with the archive settings that you want. See the following sections for more information about nodes and options:
Can I perform VSS operations in a clustered SQL Server environment?
Yes, you can run Data Protection for SQL Server VSS operations in a clustered SQL Server environment. For more information, see Failover clustering and AlwaysOn Availability.
How can I perform VSS offloaded backups or manage local snapshots?
Install Data Protection for SQL Server to perform VSS offloaded backups, or to back up and restore local snapshots. For more information, see Prerequisites.
How can I use VSS and legacy backups together in a common backup strategy?
For more information, see Choosing your backup strategy.
Can I use legacy backups and VSS backups together?
Yes, you can apply legacy differential and legacy log backups after a full VSS backup has been restored. In order to do this, you must leave the database in a recovering state by specifying /recovery=no on the command-line interface or by making sure that the Recovery option in the graphical-user interface Restore Databases or Restore Groups/Files is not selected when restoring the VSS backup. With VSS, you can run only full backups. You cannot run log, differential, individual filegroups, individual files, and set backups with VSS. For more information, see Choosing your backup strategy.
When restoring very large SQL Server databases, how can I prevent the restore operation from failing due to a timeout error?
SQL Server rebuilds and formats new physical files into which the backup data is restored. Because this process can continue for more than an hour for large databases, the IBM Storage Protect session might timeout and cause the restore process to fail. To prevent such a failure, set the value of the IBM Storage Protect COMMTIMEOUT option to 3600 or higher. Set the value to 10000 or higher for databases larger than 100 GB. For a LANFREE restore operation, increase the value of both the COMMTIMEOUT and IDLETIMEOUT options for the Storage Agent.
How does VSS instant restore operations work?
In a VSS instant restore operation, a volume-level hardware-assisted copy that includes target volumes (that contain the snapshot) are copied back to the original source volumes. A SAN Volume Controller, Storwize® family, XIV®, or IBM® System Storage® DS8000® series storage system is required to complete VSS instant restore operations. For more information, see VSS instant restore processing.
Now that I am performing VSS operations, why are there so many active backups?
IBM Storage Protect policy manages VSS backups that are located on local shadow volumes and on IBM Storage Protect server storage. With this feature, you can use different policies that can lead to an increase in the number of active backups. For more information, see How policy affects backup management on Data Protection for SQL Server and Choosing your backup strategy.
Why do I receive a TCP/IP timeout failure when I have Windows™ internal VSS tracing turned on?
Data Protection for SQL Server VSS operations might timeout with a TCP/IP failure when Windows internal VSS tracing is turned on because of the additional time required to write entries to the trace file. You can avoid this issue by increasing the values for the IBM Storage Protect server commtimeout and idletimeout options or by decreasing the amount of Windows internal VSS tracing.
What are the settings to use for optimal performance?
The default value of the buffers parameter (3) and the buffersize parameter (1024) have demonstrated the best performance in testing. However, environment factors such as network speed, physical database layout, machine resources, and SQL Server resources all affect Data Protection for SQL Server performance and should be considered when determining your settings. Note that the buffers and buffersize parameters apply to legacy backups only. For more information, see the following topics:
How do I schedule Data Protection for SQL Server backups?
You can schedule Data Protection for SQL Server backups by using the IBM Storage Protect backup-archive client scheduler or MMC scheduler.
How do I set up Data Protection for SQL Server to run in a cluster?
The following sections contain information about using Data Protection for SQL Server in a cluster environment:
How do I know if my backup ran successfully?
A message displays that states the backup completed successfully. In addition, the Task Manager in MMC provides centralized information about the status of your tasks. Processing information is also available in the following files:
  • Data Protection for SQL Server log file (default: tdpsql.log)

    This file indicates the date and time of a backup, data backed up, and any error messages or completion codes.

  • IBM Storage Protect server activity log

    Data Protection for SQL Server logs information about backup and restore commands to the IBM Storage Protect server activity log. An IBM Storage Protect administrator can view this log for you if you do not have an IBM Storage Protect administrator user ID and password.

  • IBM Storage Protect API error log file (default: dsierror.log).
Should I use the same nodename as used by my backup-archive client?

Legacy backups: Use different node names to simplify scheduling, data separation, and policy management tasks.

VSS backups: You must use different node names.

For more information, see Specifying Data Protection for SQL Server node name parameters.

How do I set up LAN Free to back up Data Protection for SQL Server over my SAN?
See the LAN-free section in Performance tuning.

For more information, see http://www.redbooks.ibm.com/abstracts/sg246148.html.