Backup and Recovery of MSSQL databases with TSM TDP

If you are using TSM, you have three options available to get a good backup of an SQL database

  1. Stop the SQL Server and backup the databases with the TSM Windows client - lots of messy scripting
  2. Using the MSSQL backup utility, back the database up to a file on disk, then back that file up to tape using the TSM Windows client - needs extra disk space, and a way to resolve the scheduling issues between database backup and TSM backup
  3. Back the database up with the TDP for MSSQL databases

I'd recommend the third method as it is simple, allows you to backup the databases with the SQL server active, and interfaces with the SQL server utilities. This page discusses using Tivoli Data Protection (TDP) for MSSQL databases

The TDPSQL client will only backup SQL databases, you also need a normal TSM client to backup all the non-database files on the Windows server.

Backing up SQL databases

Scheduling backups

You need three different Windows services defined when you want to run SQL TDP backups on a server. If you are using the Tivoli Client Acceptor then you will have this defined as a Windows service, to start automatically at system boot time. The actual TSM client scheduler runs as a Windows service but is not started automatically, it runs in manual mode. These two are the traditional services used for general file backups.
You also need a TSM TDPSQL Scheduler service, which must be started automatically at system boot time.
You will start the services manually when you first set the TDP up. It seems that the Client Acceptor must be started before the TDPSQL service or the schedule misses.

The TDPSQL scheduler does not backup the databases directly, but executes a command file. This file is defined in your TSM schedule and needs to have the same name for all your windows servers, unless you use multiple schedules. A suggested file name is c:/tdpsql.exe
Within the exe file, you will have commands to backup the databases. The option below shows how to take a full backup of all databases defined to this server. There are lots of other backup options, including differential, partial, file based and transaction log. Take a look at the MSSQL database backup section for details. The command for a full backup is

cd /d c:\program files\Tivoli\tsm\tdpsql tdpsqlc backup * full

The first line navigates to the location of the TDPSQL code, and you should change this to match your location. The second line invokes the tdpsqlc command to take a full backup of all databases. The '*' means all databases
Another possible command option to just backup changed data in the northwind database is

tdpsqlc backup northwind differential

These commands assume that you are only running one instance of SQL server on your machine. If you run two or more servers then these commands will only backup the default instance. You need to specifically select each instance with the /SQLSERVER switch.

Manually backing up databases

There are two types of MSSQL backups these days, legacy and VSS.

Taking a legacy backup

  • Open up the TDP for SQL Data Protection GUI using Start - Programs - Tivoli Storage Manager - Data Protection for Microsoft SQL server - SQL Client Gui. If you are running in either a Microsoft or Veritas cluster you need to use the /sqlserver option.
  • The GUI should default to the 'Backup Databases' tab, but if not, click on it
  • The server options are on the left hand side of the window. Expand the SQL server tree to see the individual databases
  • Click on the database in the server tree that you want to backup.
  • If you click on the SQLserver node at the second level of the tree, you will backup all the databases. Databases that will be backed up will be highlighted yellow, with a red tick.
  • Select 'Legacy Backup' from Backup Options section.
  • Select the backup type you want by clicking in the appropriate backup radio button
  • Click on the backup button

You can also use the SQL Server Management Studio (SSMS) GUI for scheduled or manual backups.

When you define the TSM node for the SQL database. you will see an option called BACKDELETE. This should be set to YES then TSM will clean up backups that have failed.

Handling the backup Metadata

The Backup metadata includes stuff like the names and sizes of database files and file groups. If you want to query a backup to find out what is on the tape, or checkout the database schema for that backup, it is a real pain to have to wait for a tape to be mounted to get that info. You should send the meta data to a disk only storage pool. These meta data objects are always quite small, usually less than 100K and to put them to a tape is not good for performance. You put them to disk by binding the meta objects to a management class that points to a disk only storage pool. That is done using the INCLUDE option. For example

INCLUDE "\...\meta\...\" MetaMgmtClass

Where 'MetaMgmtClass' is the management class that writes to a disk only storage pool.

VSS backups

TSM can use Microsoft Volume Shadow Copy Services to backup MSSQL databases. VSS takes a space efficient snapshot copy of the database using pointers, so the database is in backup mode for a very short time, even for very large databases. You have two options with VSS backups, you can keep the backup on a local disk for rapid restore, or you can offload it to a remote server. If you chose to keep it on local disk then you must make sure that sufficient space is available to support it. If you chose to use TSM with VSS, then you need the TSM for Copy Services module licensed.

To take a VSS backup manually

  • start up the TSMTDP GUI as for a legacy backup
  • Click on Edit - Configuration - VSS Backup Tab and make sure that a Local DSMAGENT Node name is specified.
  • The server options are on the left hand side of the window. Expand the SQL server tree to see the individual databases
  • Click on the database in the server tree that you want to backup.
  • If you click on the SQLserver node at the second level of the tree, you will backup all the databases. Databases that will be backed up will be highlighted yellow, with a red tick.
  • Select the VSS Backup method from the Backup Options section and chose which storage type you want from the Backup Destination drop-down menu. The back type has to be full.
  • Click on the backup button

Recovering SQL databases

Restoring a single database

This restore method works for either legacy of VSS restores

  • Open up the TDP GUI
  • Click on the 'Restore Databases' tab
  • Click on the 'show inactive and active backup' radio button
  • The server options are on the left hand side of the window. Expand the SQL server tree twice to see the individual databases. The first click will show you the SQL servers that are on that node.
  • Click on the database in the server tree that you want to restore. Databases that will be restored will be highlighted yellow, with a red tick.
  • Pick out the database that you want to restore then click the plus sign to the left of it. You will then see what kinds backups are available for your restore. You can only do a VSS restore if there are full VSS backups available.
  • Click on the backup file you want from the file list box in the lower right. you can select full, differential or log backups if these are available. The file list will show backup dates.
  • If you right click on the backup, you will get another menu to decide where you want the restore to go to. For legacy restores, you can also select a point in time to restore to.
  • Click on the restore button

It takes a while for the restore process to start, it will hang for 3-4 minutes with a message 'waiting for the TSM server'. The restore is waiting for a tape to be mounted in the automated tape library.

As the TSM backups are recognised by the SQL server, it is also possible to run a restore using the SQL Server Management Studio (SSMS) recovery utility.

Restoring the whole SQL Server

To restore the entire system, the 'master' database must be restored first, with the dbms in single user mode. If necessary you restore the model and msdb databases next, then finally the application databases.

  • Stop the DBMS
  • Start it again in single user mode, from a DOS window. The command is 'sqlservr.exe -m'
  • Restore the master database using the GUI as above. When the restore completes, it will close the sqlserver automatically, and then issue an error message that it has lost communication to the database. This is expected, as the restore just shut the database down. If you see 'restore completed successfully', all is well.
  • Start the sqlserver in normal mode
  • Restore the msdb and model databases using the GUI. You can click on both, and restore them with a single operation. These restores should all work without errors.
  • Restore the application databases using the GUI. Again, they can all be restored in a single operation. These restores should all work without errors.

You must have SQLserver running to do TDP restores, and the server must have a minimum of a master, msdb and model databases allocated. If the SQL server is totally goosed, you have to rebuild the master database from the SQL install CD. The Install CD has to be at the same SQL code level, release level and stuff as the server that you are restoring from. You also need to know the schema definition of the database, which must EXACTLY match the SQL server that you are restoring from. You can find all these definitions by using the TDP command

tdpsqlc query tsm master full /fromsqlserver=xxxxxxxx /tsmnode=xxxxx /tsmpw=xxxxxx /mountwait=yes /fileinfo /compatibilityinfo

If you are recovering a database to a new location, the restore goes through a reformatting process at the end, and this can take a while. This was an especial problem with large databases prior to SQL2005, as the MSSQL server had to format the database files before the recovery could start. To prevent the restore from timing out, you may need to up the communication timeout parameter on the TSM server. It is usually set to 30 minutes, but that may not be enough. Use the SETOPT COMMT command on the TSM server to increase it.

Recovering to another server

The easiest way to restore SQL databases to a different Windows server is just to copy the dsm.opt file from the source to the target server, so the target server thinks that it is the source as far as TSM is concerned. If you do this, rename the dsm.opt file on the target server first, so you can put it back when you are finished. You will also need to copy the tdpsql.cfg file, again renaming the original first for backout.
You can also do new server restores with commands, but you need to tell TSM that it is recovering to both a different TSM node, and a different SQL server. You may also need to issue the command

SET ACCESS BACKUP * TARGET_TSMNODE

from the DSMC command line on the source tsm node

You may need to temporarily comment out PASSWORDACCESS GENERATE in the dsm.opt file in the tdpsql directory, as you have to supply the password of the other node. Remember to put back PASSWORDACCESS GENERATE when you are finished.

You can use the GUI for cross server restores by pointing it to a different dsm.opt file which contains a different node name. Or you can the TDP command line. Open a DOS window, and 'cd' it to the tdpsql directory with the dos command

cd /d c:\program files\Tivoli\tsm\tdpsql

If you want to restore a single database, it must be registered on the target SQL server with the same name as the source. Database names are case sensitive If you want to restore the whole system, you start with the master database as above. The syntax of the command to restore a database is

tdpsqlc res db_name full /fromsqlserver=source_server /tsmnode=source_nodename /tsmp=password_of_source_node

This will restore the database to the same drive letter and path. If you want to go to a different physical location than from the original backup using commands then you need to add /FROMSQLSERVER in the command list.
If you are restoring from the GUI it will default to backups from the current server. To find backups from a different server, click on the RESTORE DATABASES tab to change the server name.
You can specify the /SQLSERVER= option to specifically state which SQL Server you want your restored databases to go to.

/relocate /to=drive:new\file\location\

You can also use combinations of /into=dbname /replace to force an overwrite of an existing database. Use /into to restore a database to a different logical database name (as opposed to a different physical location).

If you use /into=newdbname and the newdbname already exists, you need to use /replace. You also use /replace if your target file exists and is different from the source file. Does all this sound confusing? Try reading the manual! Here's a couple of examples

To restore the master database from an MSSQL database server called CLS_canary that was on tsm client called Norwich with password kettle4, onto a TSM client called Ipswich running an MSSQL database server called CLS_tractor, you need the SQL server started in single user mode, and the actual command is

tdpsqlc res master full /fromsqlserver=CLS_canary /tsmnode=Norwich /tsmp=kettle4 /replace

You don't specify your local node and server, as you are 'pulling' the restore from there.

To restore a user database called Keeper from the CLS_canary MSSQL database server, to a different path, d:\devdb\copy\norwich\ on the Ipswich server, to replace an existing database called Striker, you run the Ipswich SQL server in normal mode, and the command is

tdpsqlc res Keeper full /fromsqlserver=CLS_canary /tsmnode=Norwich /tsmp=kettle4 /relocate /to=d:devdb\copy\norwich\ /into=Striker /replace

Remember, to restore between databases, the database schemas must match.

Cross Server Database restore problem

Sometimes you can get a problem when trying to restore a database onto a new server, using the /relocatedir option to move the database to a new location. In our case we had /relocatedir=g:\restore\ but the restore kept trying to put the database back in the original location, then failed because the files were in use (luckily!). The error message said something like 'file xxxx cannot be overwritten, it is being used, use WITH MOVE to identify a valid location'.

WITH MOVE does not seem to exist, but the solution was to identify all the database components and add them to the /relocate option as component names and the /to option as path/file names like this

/relocate=SDB_UR,SDB_UR2,SDB_UR3
/to=g:\restore\SDB_UR.mdf,g:\restore\SDB_UR2.ndf,g:\restore\SDB_UR3.ndf

VSS restores

There are two types of VSS restores, 'instant' restores that uses hardware snap shot features, and 'fast' restores that use backups from local shadow volumes. Both these restores are full database, not incremental or differential restores. Both these restores also require that the the transaction logs be applied, so while the database restore might be instant or fast, the overall recovery time might take a while.

Buffers and performance tuning

The TSM TDP uses two sets of buffers to stage the data that it is restoring. The data is initially stored in the SQLBUFFERS then transferred to the TDPBUFFERS before being passed over to the TSM server. You can adjust the SQL buffer settings with the restore parameters BUFFERS=n and BUFFERSIZE=kk, where n is a value between 2 and 8, and kk is between 64 and 8192. The default values are 3 and 1024 respectively. You can potentially improve restore performance by using more, larger buffers but if you start hitting the memory limits on the SQL server and it starts paging, then performance will be worse.