IBM Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server V6.4 Documentation Updates

Product documentation


Abstract

This document describes the documentation updates for IBM Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server Version 6.4.

Content

(IT03801) For Microsoft SQL legacy backups, when changing 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 changing the management class for either Log or Set backups.

-----------------------------------------------------------------------------------------------------------------------------------------------
The following information is to be added to the 'Protecting data' section ->

Restoring databases with full-text catalogs and indexes

When protecting SQL Server 2005 and the full-text index is part of a full-text catalog note that the full-text catalog has a physical path. In this scenario, the full-text catalog is treated as a database file.

When protecting SQL Server 2008 and later data, a full-text catalog is either a logical or virtual object that contains a group of full-text indexes. This full-text catalog does not have a physical path. In this scenario, the full-text catalog is part of the filegroup.

To restore a database with the SQL Server 2005 physical full-text catalog file from the command-line interface, use the /RELocate and /TO parameters. For example:
Restore DATABASE full /relocate=database,sysft_docindex,database_log /TO={database_dir}\database.mdf,{database_dir}\docindex,{database_log_dir}\database_log.ldf

From the GUI, for SQL Server 2005 physical full-text catalog files, you can use the Relocate files individually option. From the command-line interface, use /relocate and /TO instead of /RELOCATEDir.

To restore a database with SQL Server 2008 and later full-text catalog files, no data is explicitly stored. The file is automatically backed up and restored as part of the filegroup.

-----------------------------------------------------------------------------------------------------------------------------------------------

The following links contain information related to the tdpsqlc backup command that is updated:


The tdpsqlc backup syntax diagram should be used with the following notes:

Notes:
    /backupmethod is only valid when using the full or copyfull positional parameters.

    Full and copyfull backups can be performed using VSS or legacy operations. The /backupmethod parameter is used to choose between the options.
    Log, diff, file and group backups can be performed only when using legacy operations. The /backupmethod parameter is not supported in with these types of backups because only legacy backups are viable.

    The /backupdestination parameter is valid only when using the full or copyfull positional parameters.

    Full and copyfull backups can be saved to local storage, TSM server storage, or both. The /backupdestination parameter is used to choose among the options.

The following parameter descriptions apply to the tdpsqlc backup command. These descriptions supercede information originally published:

/BACKUPDESTination=TSM|LOCAL|BOTH
    Use the /BACKUPDESTination parameter to specify the location where the backup is stored.

    You can specify:
    TSM
      The backup is stored on Tivoli® Storage Manager server storage only. This is the default.
    LOCAL
      The backup is stored on local shadow volumes only. This is only valid when the /BACKUPMETHod parameter specifies VSS.
    BOTH
      The backup is stored on Tivoli Storage Manager server storage and local shadow volumes. This is only valid when the /BACKUPMETHod parameter specifies VSS.

    Note: The /BACKUPDESTination parameter is valid only when using the full or copyfull positional parameters.

    Full and copyfull backups can be saved to TSM server storage, local storage, or both. The /BACKUPDESTination parameter is used to choose among options.
    Log, diff, file and group backups can be stored only to TSM server storage. In this scenario, the /BACKUPDESTination parameter is not supported because TSM is the only viable option.

/BACKUPMETHod=LEGACY|VSS
    Use the /BACKUPMETHod parameter to specify the manner in which the backup is performed.

    You can specify:
    LEGACY
      The backup is performed with the legacy API. This method is the SQL streaming backup and restore API as used in previous versions of Data Protection for SQL. This option is the default value.
    VSS
      The backup is performed with VSS.

    Note: The /BACKUPMETHod parameter is valid only when using the full or copyfull positional parameters.

    Full and copyfull backups can be performed using VSS or legacy operations. The /BACKUPMETHod parameter is used to choose between the options.
    Log, diff, file and group backups can only be performed using legacy operations. In this scenario, the /BACKUPMETHod parameter is not supported because legacy is the only viable option.

-----------------------------------------------------------------------------------------------------------------------------------------------

A paragraph is missing from the Planning for Data Protection for Microsoft SQL Server operations -> Backup strategies topic. This topic is available in the following locations:
The following statement is missing from the original publication:

The PRIMARY filegroup must be restored prior to restoring a user-defined filegroup. Ensure that you are able to restore the PRIMARY filegroup backup by taking a full backup or a group backup of the PRIMARY filegroup before taking the user-defined backup.

The following information is not missing in the published document, but is key to this discussion: File, group, and set backups must be followed by a log backup, but a full is not required.
-----------------------------------------------------------------------------------------------------------------------------------------------

A paragraph is missing from the Protecting Microsoft SQL Server data -> Restoring SQL databases and files -> Restoring SQL file groups and files from Legacy backups topic. This topic is available in the following locations:
The following statement is missing from the original publication:

Microsoft SQL Server requires that the PRIMARY filegroup is restored before or along with a user-defined filegroup. To bring the database back to a usable state a log restore must be performed after the user-defined filegroup is restored.
-----------------------------------------------------------------------------------------------------------------------------------------------
The legacy examples in the "Examples of Tivoli Storage Manager policy binding using include/exclude and VSSPOLICY statements" topic have been updated. The backup objects are processed differently from earlier releases.

This topic is available in the following locations: The following information includes all updated legacy examples:

Legacy examples

Legacy backups use INCLUDE/EXCLUDE statements in the Data Protection for SQL options file. For the examples provided in the following tables, grouped statements are intended to be used together. For example:
    \...\full*

    \...\full*\*

and
    \...\file\f1*\*

    \...\file\f1*

    \...\f1\file*
Object matches for backuptype Specification
Example for all objects \...\*
Example for exclude statements with all type of backups
(full, diff, log, group, file, set)
\...\full*



\...\diff*
Example for include and exclude statements with all type of backups (full, diff, log, group, file, set) \...\full*

\...\full*\*



\...\copyfull*

\...\copyfull*\*



\...\diff*

\...\diff*\*



\...\log*

\...\log\...\*



\...\group*

\...\group\...\*



\...\file*

\...\file\...\*



\...\set*

\...\set\...\*
Example for exclude statements with file (f1) and group (g1) \...\g1\group*

\...\f1\file*
Example for include statements with file (f1) and group (g1) \...\group\g1*\*

\...\group\g1*

\...\g1\group*



\...\file\f1*\*

\...\file\f1*

\...\f1\file*
Example for exclude statements with group or file object names beginning with g or f \...\g*\group*

\...\f*\file*
Example for include statements with group or file object names beginning with g or f \...\group\g*\*

\...\group\g*

\...\g*\group*



\...\file\f*\*

\...\file\f*

\...\f*\file*
Example for exclude statements same as \...\group* or \...\file* (there is no equivalent for include statements) \...\*\group*

\...\*\file*

backuptype object with database matches Specification
Example for all objects with database name Db1 \...\Db1\...\*
Example for all objects with database name Db1 beginning with Db \...\Db*\...\*
Ambiguous \...\*\...\*
Example for exclude statements using full, diff, copyfull objects with database name Db1 \...\Db1\full*

\...\Db1\copyfull*

\...\Db1\diff*
Example for exclude and include statements using full, diff, copyfull objects with database name Db1 \...\Db1\full*

\...\Db1\full*\*



\...\Db1\copyfull*

\...\Db1\copyfull*\*



\...\Db1\diff*

\...\Db1\diff*\*
Example for exclude statements using log, group, file, set objects with database name Db1 \...\Db1\...\log*

\...\Db1\...\group*
Example for exclude and include statements using log, group, file, set objects with database name Db1 \...\Db1\...\log*

\...\Db1\...\log*\...\*



\...\Db1\...\group*

\...\Db1\...\group*\...\*



\...\Db1\...\file*

\...\Db1\...\file*\...\*



\...\Db1\...\set*

\...\Db1\...\set*\...\*
Example for exclude statements using all group or file object names (g1, f1) with database name Db1 \...\Db1\g1\group*
\...\Db1\f1\file*
Example for exclude and include statements using all group or file object names (g1, f1) with database name Db1 \...\Db1\group\g1*

\...\Db1\group\g1*\*

\...\Db1\g1\group*



\...\Db1\file\f1*

\...\Db1\file\f1*\*

\...\Db1\f1\file*
Example for exclude statements using all group or file object names beginning with g or f with database name Db1 \...\Db1\g*\group*

\...\Db1\f*\file*
Example for exclude and include statements using all group or file object names beginning with g or f with database name Db1 \...\Db1\group\g*

\...\Db1\group\g*\*

\...\Db1\g*\group*



\...\Db1\file\f*

\...\Db1\file\f*\*

\...\Db1\f*\file*
Example for exclude statements using \...\Db1\...\group* or file* (there is no equivalent for include statements) \...\Db1\*\group*

\...\Db1\*\file*
Example for exclude statements using \...\Db1\full* \...\Db1\...\full*
Example for exclude and include statements using \...\Db1\full* \...\Db1\...\full*

\...\Db1\...\full*\*
Example for exclude statements using \...\full* \...\*\full*
Example for exclude and include statements using \...\full* \...\*\full*

\...\*\full*\*
Example for exclude statements using \...\group* (there is no equivalent for include statements) \...\*\*\group*
Example for exclude statements using \...\g1\group* (there is no equivalent for include statements) \...\*\g1\group*
Ambiguous \...\*\...\log*
Nothing (typeInfo missing) \...\Db1\set*

For the following table, use the following guidelines:
  • If you use using only exclude statements with only \meta\, all objects (including data) are excluded.
  • If you are using only exclude statements with only \data\, errors occur.
Meta and data object matches Specification
Example for all meta or data objects \...\meta\...\*

\...\data\...\*
Example for all meta full objects \...\meta\...\full*
Example for all data full objects \...\data\...\full*

\...\data\...\full*\*
Example for all meta group object names (g1) \...\meta\...\g1\group*
Example for all data group object names (g1) \...\data\...\group\g1*

\...\data\...\group\g1*\*
Example for all meta group object names beginning with g \...\meta\...\g*\group*
Example for all data group object names beginning with g \...\data\...\group\g*

\...\data\...\group\g*\*
Same as \...\meta\...\group* \...\meta\...\*\group*
Nothing (qualifiers missing) \...\meta\*\...\data\*

Meta and data object with database matches Specification
Example for all meta or data objects with database name Db1 \...\meta\...\Db1\...\*

\...\data\...\Db1\...\*
Example for all meta objects with database name Db1 \...\meta\...\Db1\full*
Example for full objects matching all data objects \...\data\...\Db1\full*

\...\data\...\Db1\full*\*
Example for all meta objects with database name Db1 \...\meta\...\Db1\...\log*
Example for all log objects matching all data objects \...\data\...\Db1\...\log\...\*
Example for all group matching all meta objects \...\meta\...\Db1\...\group*
Example for group matching all data objects \...\data\...\Db1\group\...\*
Example for all meta object names (g1) with database name Db1 \...\meta\...\Db1\g1\group*
Example for all data group object names (g1) with database name Db1 \...\data\...\Db1\group\g1*
Example for all meta object names beginning with g with database name Db1 \...\meta\...\Db1\g*\group*
Example for all data group object names beginning with g with database name Db1 \...\data\...\Db1\group\g*
Same as \...\meta\...\Db1\...\group* (No equivalent for data objects) \...\meta\...\Db1\*\group*
Same as \...\meta\...\full* (No equivalent for data objects) \...\meta\...\*\full*
Same as \...\meta\...\group* (No equivalent for data objects) \...\meta\...\*\*\group*
Same as \...\meta\...\g1\group* (No equivalent for data objects) \...\meta\...\*\g1\group*
Ambiguous \...\meta\...\*\...\log*

\...\data\...\*\...\log*
Nothing (qualifiers missing) \...\meta\*\...\data\*

Server matches Specification
Example for all objects from all servers beginning with SQL SQL*\...\*
Example for all objects from all server instances with host SQL2012 SQL2012\...\*
Example for all objects from server SQL2012\INST1 SQL2012\INST1\...\*
Example for all objects from all servers beginning with SQL2012\INST SQL2012\INST*\...\*
Same as SQL2012\...\* SQL2012\*\...\*
Example for all meta or data objects from server SQL2012\INST1 SQL2012\INST1\meta\...\*

SQL2012\INST1\data\...\*
Example for all meta or data objects from all named server instances with host SQL2012 SQL2012\*\meta\...\*
Example for all meta or data objects from all server instances with host SQL2012 SQL2012\...\meta\...\*
Example for all objects from server default instance (if no instance name matches ??ta) SQL2012\meta\...\*

SQL2012\data\...\*

-----------------------------------------------------------------------------------------------------------------------------------------------

For the 6.4 information deliverables, in the following topics, when you read the /QUERYNode parameter, add the BOTH option:
  • Data Protection for Microsoft SQL reference -> Query command -> Query optional parameters
  • Data Protection for Microsoft SQL reference -> Restore command -> Restore syntax
  • Data Protection for Microsoft SQL reference -> Restore command -> Restore optional parameters

In summary, for the /QUERYNode parameter, there are three options:
  • DP
  • ALWAYSON
  • BOTH

For convenience, here's the description of the /QUERYNode parameter: Use to specify whether you want to query standard databases from SQL Server
2012 that were backed up from a standard Data Protection for SQL node or
the AlwaysOn node. This parameter is ignored for availability databases
because the availability databases are always backed up under the
AlwaysOn node.

-----------------------------------------------------------------------------------------------------------------------------------------------

Throughout the 6.4 information deliverables: The specification for the DATEformat values are not consistent.

Correct specification for DATEformat values:

DATEformat= dateformatnum


The /DATEformat parameter selects the format you want to use to
display
dates.


The dateformatnum
variable can range from 1 to 7. The initial
value is 1.


The number values specify the following formats:

1 MM/DD/YYYY

2 DD-MM-YYYY

3 YYYY-MM-DD

4 DD.MM.YYYY

5 YYYY.MM.DD

6 YYYY/MM/DD

7 DD/MM/YYYY

Incorrect specification for DATEformat values:

DATEformat= dateformatnum


The /DATEformat parameter selects the format you want to use to
display
dates.


The dateformatnum
variable displays the date in one of the following formats. Select the format number that corresponds to the format you want to use.

The number values specify the following formats:

1 MM/DD/YYYY. This is the default.
2 DD-MM-YYYY

3 YYYY-MM-DD

4 DD.MM.YYYY

5 YYYY.MM.DD
-----------------------------------------------------------------------------------------------------------------------------------------------
Add the following topic to the "Planning for Data Protection for Microsoft SQL Server operations" section:

Planning for data protection

For Microsoft SQL Server data protection, one of the most important tasks that you need to complete is the routine full database backup of the master database. You can schedule routine full database backups so you frequently protect the data to meet your business needs.

If the master database is damaged while a server instance is running, fix the damaged database by restoring a recent full master database backup. If a server instance cannot start because the master database is damaged, the master database needs to be rebuilt. When you rebuild a master database, all system databases revert to their original state. For more information about rebuilding the master database, see the Microsoft SQL Server documentation.

-----------------------------------------------------------------------------------------------------------------------------------------------

Replace the "Restoring the master database" topic in the "Protecting Microsoft SQL Server data" section with the following topic:

Restoring the master database

To restore a master database, start an instance of SQL Server in single-user mode. You cannot restore a master database when the SQL Server instance is set to multi-user mode.

When the SQL Server instance is in single-user mode, the command line interface, tdpsqlc.exe, should be used. The Microsoft Management Console (MMC) GUI, included with FlashCopy Manager for SQL / Data Protection for SQL, does not support interacting with a SQL server instance started in single-user mode.

To restore the master database, complete the following steps:
1. Click Start > All Programs > Tivoli Storage Manager > Data Protection for Microsoft SQL Server > SQL Client - Command Line.
2. Start the SQL Server in single-user mode. For more information about SQL Server startup options, see the Microsoft SQL Server documentation. For example, the following link might provide useful information: http://technet.microsoft.com/en-us/library/ms190737.aspx
3. Use Data Protection for SQL to restore the master database. When the master database finishes the restoration process, the SQL Server shuts down and an error message is displayed. The message indicates that the connection to the SQL Server is lost. This loss of connection is expected.
4. Restart the Database Engine to restore SQL Server to the typical multi-user mode.
5. Manually reapply any changes that were made to the master database after the date of the database backup used to do the restore operation.

After the master database is restored, the MMC can be used to back up and restore individual databases that are operating in single-user mode.

Rate this page:

(0 users)Average rating

Document information


More support for:

Tivoli Storage Manager for Databases
Data Protection for MS SQL

Software version:

6.4

Operating system(s):

Windows

Software edition:

All Editions

Reference #:

7035973

Modified date:

2014-08-16

Translate my page

Machine Translation

Content navigation