Tivoli TDP for SQL is a great backup tool that allows you to take live SQL database backups directly to your backup system, negating the need to perform SQL data dumps to disk first.
TDP SQL Client GUI restores:
The backup side works very well, however restoring SQL databases that use multiple data files is not quite as straight forward as you might imagine as there is a bug in the GUI (client version 5.5.5) where it only support/recognises 2 data files.For basic SQL databases this is fine as its the data & log file (mdf & ldf files), its also not a problem if you are using multiple data files & want to restore over the top of an existing database.
Lets say you want to do a test restore of that really important customer database to a new DB though, in the GUI you can restore into a new name & relocate the files to a new folder….but if you use multiple data files, the GUI only attempts to relocate the first two datafiles, the rest it attempts to restore in the original (source database) folder location – which will fail as its already in use.
TDP SQL Command Line restores:
The solution is to do a command line restore, which is actually quiet straightforward once you’ve done it before. Here is an example:
tdpsqlc restore LiveDB1 full /into=TestDB1 /REL=LiveDB1_datafile /TO=D:\Test_restore\LiveDB1_datafile.mdf /REL=LiveDB1_datafile1 /TO=D:\Test_restore\LiveDB1_datafile1.ndf /REL=LiveDB1_datafile2 /TO=D:\Test_restore\LiveDB1_datafile2.ndf /REL=LiveDB1_logfile /TO=D:\Test_restore\LiveDB1_logfile.ldf
While it looks like there are line returns in the version above to make it more readable this should be entered as a single line in the TDP Command Prompt window when you run it.
Essentially the command restores the last full backup of DB “LiveDB1” to a new database named “TestDB1” & relocates each data/log file to a new directory “D:\Test_restore\” using the /REL & /TO switches. When using the /REL= switch this needs to use the Logical Name found on the “Files” section of the database properties. That switch is followed by the /TO= switch detailing the physical location & filename you are restoring it too. This should be different from the original DB being restored from. For the database LiveDB1 the logicalnames & physical files used are:
LiveDB1_datafile – LiveDB1_datafile.mdf
LiveDB1_datafile1 – LiveDB1_datafile1.ndf
LiveDB1_datafile2 – LiveDB1_datafile2.ndf
LiveDB1_logfile – LiveDB1_logfile.ldf