Recovery of individual SQL database and log files in SQL

[[email protected]]

Question from our SQL DBA:

Using Tivoli Data Protection for Microsoft SQL Server, is it possible to recover an individual backup file (database or log), without doing a database restore? We want to be able to recover particular files (“.bak” or “.trn” ) from TSM and then have those files available on the server as they would be if they were created with the native SQL Server Backup API. Is this possible to accomplish with a backup file created by TDPSQL?

 

[fuzzballer]

I am not certain about your question but would like to make a comment on backing up databases. We have always covered ourselves both ways. Within SQL we would create a sql backup and pick that up with the filesystem backup and also run the TDPSQL backup. This way if one fails or has an issue with retoration the other is available. I have only restored the entire database when using TDPSQL so I am not certain about individual tables. I don't believe that is the way it works. I would be intersted in knowing this too.

 

[[email protected]]

That's basically what's being done here. But in an effort to prepare for an intrusion event, for instance, we want to have the ability to look at the logs using 3rd party utilities and would prefer not to rely on two backup scenarios to accomplish everything we need.

 

[[email protected]]

Recovery of individual SQL database and log files in SQL (Answer from IBM)

From IBM level 2 TDP support:

The short answer to their question is "no".

The SQL Server has the built in capability to perform a backup. This basically exports the data to a file on the harddrive.
If this file was created with the SQL Server, then the TSM BA client could be used to backup this file to the TSM Server. And of course the BA client would then be used to restore this file back to the hard drive for any restore that might be necessary using the SQL Server Backup/Restore interface (note that there is no TDP in this picture).

If the customer wants to use the TDP to backup the file, but the SQL Server interface to restore it. This is not possible.
The problem is that the TDP does not have the data in the same format at the SQL Server would place on the hard drive.
AND the TDP does not have the capability to place a file on the hard drive.
When the TDP performs a backup, it gets the data from the SQL Server. The SQL Server is just scooping the data out of the files and sends this chunk of data along with a package on top of it to tell the SQL Server what this data is. This data is not a file and has no file permissions, etc. Thus to restore it to the hard drive is not viable since it is not really a file, but just a hunk of data

 

[GregE]

Thanks for sharing this info.