Troubleshooting
Problem
Restoring differential backups when full backups and differential backups are performed via different methods.
Symptom
In this example, a native SQL Server backup was performed and the .BAK file was then backed up using the regular Tivoli Storage Manager Backup-Archive client.
Cause
The differential backup is associated to the last Full backup whether it be a Tivoli Storage Manager for databases backup or a Native SQL Server (.BAK) dump file.
Diagnosing The Problem
Obtain the SQL Server log (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG) and review this information to determine which backup vendor was used for the FULL backups.
Check the backup processing to determine which vendor performed the backup.
For example a backup that is taken with the SQL Server (.BAK) to a disk location will output a message that is similar to:
- <date/time> Backup Database backed up. Database: DB1, creation date(time): 2012/10/01(01:24:00), pages dumped: 14948643, first LSN: 28861:72220:51, last LSN: 28863:23300:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Backup\db1.bak'}). This is an informational message only. No user action is required.
Whereas a backup performed by the Data Protection for SQL will use the Virtual Device Interface with a device name that contains the "TDPSQL" designation, for example:
- <date/time> Backup Database backed up. Database: DB1, creation date(time): 2012/10/01(01:24:00), pages dumped: 15077731, first LSN: 28921:18007:83, last LSN: 28921:106499:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-000110B4-0000'}). This is an informational message only. No user action is required.
Resolving The Problem
The restore procedure for SQL Server database can vary depending of how the backups are performed.
Restoring differential backup when both the FULL and DIFF backup is performed with Tivoli Storage Manager for Databases:
1: Within the Data Protection for SQL GUI, make sure that 'auto select' is enabled (under the View menu)
2: Select the latest desired differential backup to restore and this should automatically select the last full backup it knows about. It will only know about Tivoli Storage Manager for Databases backups not Native Sql Server backups.
3: Restore the backups.
Restoring differential backup when the FULL backup performed with Native SQL Server backup (saved to the Tivoli Storage Manager server with the BA client) and the DIFF backup is taken with the Data Protection for SQL client:
1: Restore the .BAK from Tivoli Storage Manager server using the Backup-Archive client.
2: Use the native SQL Server (which performed the backup) to incorporate the restored .BAK into the SQL Server with No Recovery.
3: With the Data Protection for SQL client , restore the associated differential backup by selecting the specific DIFFFULL without 'auto select' enabled.
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21640652