Repostor Backups

Do you want to learn why Database backups are different from file backups? Click HERE to find out.

Do you want to learn why
Database backups
are different from file backups?
Click HERE to find out.

Backup and Recovery of MSSQL databases with Spectrum Protect TDP

Backing up SQL databases

Scheduling backups

You need two 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 Spectrum Protect 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 Spectrum Protect 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

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

When you define the Spectrum Protect node for the SQL database. you will see an option called BACKDELETE. This should be set to YES then Spectrum Protect 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

Spectrum Protect 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 Spectrum Protect for Copy Services module licensed.

To take a VSS backup manually

Recovering SQL databases

Restoring a single database

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 Spectrum Protect 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.

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

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 Spectrum Protect 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 Spectrum Protect that it is recovering to both a different Spectrum Protect node, and a different SQL server. You may also need to issue the command


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 Spectrum Protect client called Norwich with password kettle4, onto a Spectrum Protect 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


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 Spectrum Protect 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 Spectrum Protect 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.

back to top

Spectrum Protect pages

Lascon latest major updates