Possible data loss: Restoring a database with Data Protection / FlashCopy Manager for SQL can result in the unintentional restore of another database(s)

This document is divided into linked sections for ease of navigation. You may use the links below to jump to the desired section of the document.

Problem
Conditions Required for Problem to Occur
How Do I Know if I am Affected?
Workaround
Affected Products, Components, Releases and Fixing Levels

• Tivoli Storage Manager Backup-Archive Client
• Tivoli Storage FlashCopy Manager: FlashCopy Manager VSS Requestor

Further Assistance & Related APARs


Problem:

When using one of the affected products to restore a database whose name contains the names of another database(s), then those other databases are also restored. Any changes made to these unintentionally restored databases, from the time that the database backup copies were created through the present time, are lost because the restore process overwrites the current version of the database with the older backup copy.


For example:

If a database named db1234 is specified for restore:

All of the following DB names would also be restored, if they exist:

d, db, db1, db12, db123, db1234
b, b1, b12, b123, b1234
1, 12, 123, 1234
2, 23, 234
3, 34
4


Conditions Required for Problem to Occur

A Microsoft SQL Server database can be erroneously restored when all of the following conditions are true:

• the backup method was VSS
• the database specified for restore has a name that includes a substring that is an exact match to the full name of another Microsoft SQL Server database located within the same SQL Server instance
• the database specified for restore, and the database(s) that matches the substring of the database specified for restore, were backed up in the same backup session
• the database(s) restored in error changed the physical storage layout of the database since the time of backup

How Do I Know if I am Affected?

Review the Data Protection for Microsoft SQL Server, or the FlashCopy Manager for Microsoft SQL, log (tdpsql.log) as it includes the list of all objects restored.
Identify the output related to the restore session and look for any objects in the list of objects restored that were not intended for restore.

Below is sample restore processing output from a tdpsql.log. The statements highlighted in bold text represent the statements that you would use to identify if database names other than the one intended were restore.

============================================================ 08/19/2011 13:24:12 Request : VSS RESTORE 08/19/2011 13:24:12 Component List : 'DB1_G_FullXIV_BAS'(full, Local) 08/19/2011 13:24:12 Local DSMAGENT Node : STRINGVM1 08/19/2011 13:24:12 Mount Wait : Yes 08/19/2011 13:24:12 ------------------------------------------------------------ 08/19/2011 13:24:38 Backup(s) to be restored: 08/19/2011 13:24:38 <DB1_G_FullXIV_BAS : VSS : full : 08/19/2011 13:21:23> 08/19/2011 13:24:50 Restoring 'DB1_G_FullXIV_BAS' via file-level copy from snapshot(s). This process may take some time. Please wait. 08/19/2011 13:24:55 VSS Restore operation completed with rc = 0 08/19/2011 13:24:55 Files Examined : 2 08/19/2011 13:24:55 Files Completed : 2 08/19/2011 13:24:55 Files Failed : 0 08/19/2011 13:24:55 Total Bytes : 4194614 08/19/2011 13:25:24 ============================================================ 08/19/2011 13:25:24 Request : VSS RESTORE 08/19/2011 13:25:24 Component List : 'SQL_testmark1'(full, Local) 08/19/2011 13:25:24 Local DSMAGENT Node : STRINGVM1 08/19/2011 13:25:24 Mount Wait : Yes 08/19/2011 13:25:24 ------------------------------------------------------------ 08/19/2011 13:25:48 Backup(s) to be restored: 08/19/2011 13:25:48 <SQL_testmark1 : VSS : full : 08/19/2011 13:21:23> 08/19/2011 13:25:56 VSS Restore operation completed with rc = 0 08/19/2011 13:25:56 Files Examined : 2 08/19/2011 13:25:56 Files Completed : 2 08/19/2011 13:25:56 Files Failed : 0 08/19/2011 13:25:56 Total Bytes : 4194614

Workaround: None

However, if you have already experienced the unintentional restore of one or more databases, and have not already restarted the affected database, you can prevent data loss with the following actions.

Before the database (or databases) restored in error is restarted and additional changes are written to the database, perform the following actions:

• restore the most recent backup of the database that existed prior to the unintentional restore
• use the Microsoft SQL Server's transaction logs to roll forward to the point in time just prior to the intended restore operation
• perform a new backup of the database to preserve this state
• manually recreate any intended storage layout changes that were made between the time of the last backup created prior to the unintended restore and the actual unintended restore process

If the conditions for data loss occur, and you have already restarted the database and written additional changes since the restore, there is no workaround to
recover that data.


Affected Products, Components, Releases and Fixing Levels

The problem is seen when using either:

• Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server
• Tivoli Storage FlashCopy Manager: FlashCopy Manager for Microsoft SQL Server

to restore a Microsoft SQL Server database.

Though the problem manifests during Data Protection for Microsoft SQL Server, or FlashCopy Manager for SQL Server VSS restore operations, the affected code is located in the Tivoli Storage Manager (TSM) Backup-Archive Client (also known as the FlashCopy Manager VSS Requestor) which is a prerequisite to using Data Protection for Microsoft SQL Server and FlashCopy Manager for SQL Server VSS functions.

Therefore, the affected products / customer environments are:

• the TSM Backup-Archive Client --> when used as a prerequisite for Data Protection for Microsoft SQL Server with the TSM for Copy Services product
  Note: TSM for Copy Services is also used with Data Protection for SQL to enable VSS Backups.
• the FlashCopy Manager VSS Requestor component of the Tivoli Storage FlashCopy Manager product
  

Tivoli Storage Manager Backup-Archive Client

Release	Affected levels	First fixing level (click for download page)
5.4	5.4.0.0 - 5.4.3.3	5.4.3.4
5.5	5.5.0.0 - 5.5.3.2	5.5.3.3
6.1	6.1.0.0 - 6.1.4.1	6.1.4.2
6.2	6.2.0.0 - 6.2.2.1	6.2.2.2

Note: Publically available fix levels (e.g. interim fixes and fix packs) are cumulative within a release.


Tivoli Storage FlashCopy Manager: FlashCopy Manager VSS Requestor

Release	Affected levels	First fixing level (click for download page)
2.1	2.1.0.0 - 2.1.0.2	2.1.0.3
2.2	2.2.0.0 - 2.2.1.0	2.2.1.1

Note: Publically available fix levels (e.g. interim fixes and fix packs) are cumulative within a release.



Further Assistance & Related APARs

After reviewing this document, if you think that you may be affected and have questions, or if you are not sure whether you may be affected, contact IBM technical support for further assistance. Please reference one, or more, of the following APARs:


RESTORE OF A SQL DATABASE WITH SIMILAR NAME OF ANOTHER DATABASE USING DP FOR SQL WILL RESTORE THAT OTHER DATABASE, TOO.

# IC73088

--> APAR associated with the Tivoli Storage Manager for Copy Services when Data Protection for Microsoft SQL Server is used with Tivoli Storage Manager for Copy Services V6.1

# IC74161

--> APAR associated with the Tivoli Storage Manager for Copy Services: Hardware Integration Module when Data Protection for Microsoft SQL Server is used with Tivoli Storage Manager for Copy Services V5.5

# IC74059

--> APAR associated with the Tivoli Storage FlashCopy Manager VSS Requestor component

IBM technical support will work with you to:

• Help you determine whether your data might be affected.
• Help you locate the appropriate fix level for your product and application environment.