IBM Support

Data Protection SQL retention times for daily versus monthly backups.

Technote (FAQ)


Question

How to keep both a monthly set of SQL backups for a period of 1 year, and daily normal backup versions for 32 days.

Cause

Because the Data Protection for SQL uses the versioning functionality of Tivoli Storage Manager for the backups, a single Data Protection for SQL client can only have one set of retention values for a full backup.

Answer

To accomplish the retention of backups with 2 different retention periods a second Tivoli Storage Manager node name is needed

To configure the backups to be kept with a second node name, both Tivoli Storage Manager server configuration and client side configuration changes are required. The procedure below is the cleanest method to accomplish this goal. The naming convention is explained and may be changed to suit a specific needs.

Assume that the SQL server has been backing up to the Tivoli Storage Manager server with the name SQL-01, and that the node is registered in a specific domain SQL-DOMAIN.
On the Tivoli Storage Manager server:

  1. Create a new Policy Domain with a default backup retention of 365 days
    • DEFINE DOMAIN SQL-1-YEAR-DOMAIN DESCRIPTION="retain SQL backups for 1 year" BACKRETENTION=365
  2. Create a new Policy Set in the new domain
    • DEFINE POLICYSET SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET
  3. Define a Management Class for the new domain and policy set
    • DEFINE MGMTCLASS SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET 1-YEAR-MGMTCLASS
  4. Create a new Backup Copy Group for the management class with appropriate retention settings
    • DEFINE COPYGROUP SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET 1-YEAR-MGMTCLASS DESTINATION=your-storage-pool-of-choice VEREXISTS=13 VERDELETED=1 RETEXTRA=365 RETONLY=365
      • The Versions Exist is set to 13; this allows for a full set of 12 months plus the current backup assuming that a backup is taken once each month. It can be adjusted from 1 to 9999 or NOLIMIT to suit specific needs.
      • The Versions Deleted is set to expire all but the most recent copy of the backup when Tivoli Storage Manager detects that there are no active backups for this database. It can be adjusted 1 to 9999 or NOLIMIT to suit specific needs.
      • The Retain Extra Versions is set to 365 days. This value will maintain the 1-year retention such that even if a month is skipped the inactive copies will expire after the 1 year requirement. This can be adjusted 1 to 9999 or NOLIMIT to suit other needs
      • The Retain Only Version is set to preserve the most recent and only remaining backup for 1 year after the object has been deactivated and can also be adjusted 1 to 9999 or NOLIMIT to fit specific needs.
  5. Assign the new management class as the Default for the Domain
    • ASSIGN DEFMGMTCLASS SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET 1-YEAR-MGMTCLASS
  6. Validate and then Activate the new Policy set
    • VALIDATE POLICYSET SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET
    • Correct any errors identified by the Validate then
    • ACTIVATE POLICYSET SQL-1-YEAR-DOMAIN 1-YEAR-POLICY-SET
  7. Register a new node in the Policy Domain, using a naming convention that associates the new node with the node that does daily backups will be helpful in identifying the proper node when a restore is required. If the daily node is SQL-01 then possibly SQL-01-ONEYEAR can be used.
    • REGISTER NODE SQL-01-ONEYEAR password-here DOMAIN=SQL-1-YEAR-DOMAIN
  8. If desired, create a client schedule that automates the monthly backup, you will need the new node name and the name of the command file on the client machine to execute in order to define the schedule. This step can also be done after the client side configuration is completed.

On the Tivoli Storage Manager for Databases Data Protection for SQL client machine
  1. Create a new client options file for use with the new node name, a naming convention that is descriptive may be helpful, such as 1-year-sql.opt. the file may be located any where on the machine, but may also be left in the same directory as the current dsm.opt for the daily sql node.
  2. In the new optfile set the required options and any other options necessary options
    • NODENAME SQL-01-ONEYEAR
    • COMMMETHOD
    • TCPSERVERADDRESS
    • set the commmethod as appropriate, you may use the same settings as in the daily dsm.opt file, also set the appropriate additional options required for each commmethod, be aware that the backups do NOT have to be sent to the same Tivoli Storage Manager server as the daily backups
  3. Create a new command file or files for the backups, again an appropriate naming convention should be used, the command files already in use can be copied, but in either case the TSMOPTFILE="1-year-sql.opt" parameter must be added to the TDPSQLC command in the file. This will direct the Data Protection for Microsoft SQL to use the correct options file. You may also add the tsmoptfile parameter within the original command files to point to the default option file for safety. Also the TSMOPTFILE="opt-file-name" parameter can be used when starting the Data Protection for SQL command line or GUI to work interactively.

Once all the configuration has been established test the backups to ensure that the existing backups have not been affected and that the new backups are going to the appropriate Domain and management class.

This method is for Legacy (streaming) Data Protection for SQL backups. They do not pertain to VSS backups.

Product Alias/Synonym

TSM

Document information

More support for: Tivoli Storage Manager for Databases
Data Protection for MS SQL

Software version: Version Independent

Operating system(s): Windows

Reference #: 1624164

Modified date: 06 January 2016