TSM Backups and Restores
- Journal Backups
- Image Backups
- TSM 7.1 and VMware
- TSM 6.4 and VMware
- Windows Cluster Backups
- VCS Cluster Backups
- LAN free backups
- TDP for MSSQL DBs
- TSM with DB2
- Oracle TDP Backups
If you are using TSM, you have three options available to get a good backup of an SQL database
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.
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.
There are two types of MSSQL backups these days, legacy and VSS.
Taking a legacy backup
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.
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.
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
This restore method works for either legacy of VSS restores
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.
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.
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.
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.
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
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.
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.