This document describes how data loss can occur when restoring a Microsoft SQL Server database whose name contains the names of other Microsoft SQL Server databases. Data loss can occur for any databases that are unintentionally restored because the restore process overwrites the current version of a database with the older backup copy.This affects IBM Tivoli Storage Manager (TSM) for Databases: Data Protection for Microsoft SQL Server and IBM Tivoli Storage FlashCopy Manager (FCM) for Microsoft SQL Server.
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.
- Tivoli Storage Manager Backup-Archive Client
- Tivoli Storage FlashCopy Manager: FlashCopy Manager VSS Requestor
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.
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
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
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
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
First fixing level
(click for download page)
220.127.116.11 - 18.104.22.168
22.214.171.124 - 126.96.36.199
188.8.131.52 - 184.108.40.206
220.127.116.11 - 18.104.22.168
Tivoli Storage FlashCopy Manager: FlashCopy Manager VSS Requestor
First fixing level
(click for download page)
22.214.171.124 - 126.96.36.199
188.8.131.52 - 184.108.40.206
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.
--> 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
--> 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
--> 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.
|Storage Management||Tivoli Storage Manager for Databases||Data Protection for MS SQL||Windows||5.4, 5.5, 5.5.1, 5.5.2, 5.5.4, 5.5.5, 6.3|