TSM 6.3: Backup SQL Database

There are two  types of backup method in Tivoli Data Protection for SQL Sever: VSS and Legacy. VSS backup only supports Full, Legacy backup supports Full, Differential and Log. VSS backup supports instant restore (volume level), fast restore (file level), but it only can restore the data to the server with same name. Legacy backup does not have much limits, and it is the default backup method in the tdpsqlc command.

Example 1: Use DP For SQL Management Console

1) Login into TSM_Client01 and launch DP For SQL Management Console (it requires flash copy manager license).

2) I will start with flash copy manager. Right click the SQL server and choose properties

3) Configure the VSS Policy Binding based on your needs. In my example, I make TSM as the only backup destination.

4) Configure a SQL login account with sysadmin right in SQL server.

5) Right click the DB name and choose Full Backup.

6) After the full backup completed. The Log size is almost same, this is because the VSS Full backup does truncate the logs. To truncate log, a log backup has to be done.

7) Any DB with ‘Truncate log on checkpoint’ attribute means the log only can be truncated by SQL server itself. It is called simple recovery mode. Open the SQL management studio, and run the following SQL query against the System Database to find it out.

SELECT name, recovery_model_desc
FROM sys.databases

8) Change the recovery mode to Full of the database that I am going to backup the log. Right click the DB and choose properties.

9) Click ‘Show Backup Options’ then change the value to ‘True’ in Truncate field.

10) Right click the database and choose Legacy Full backup. The log backup only can be done after a Legacy full or differential backup.

11) After the full backup completed, right click the database and choose Legacy and Log backup.

Example 2: Use tdpsqlc

1) Login into TSM_Client01 and run C:\Program Files\Tivoli\TSM\TDPSql\tdpsqlc.exe

2) Choose the database and run a Legacy Full backup

3) Choose the database and run a Legacy Log backup.

Example 3: Schedule SQL Full Backup

1) Login into TSM_Client01 to install client scheduler (you still need to do so if there is an existing one for the normal BA client)

2) Follow the steps described before, and name it TSM SQL Client Scheduler

the dsm.opt file under C:\Program Files\Tivoli\TSM\TDPSql should be like this:

NODename TSM_Client01_SQL
PASSWORDAccess generate
TCPServeraddress TSM_Sandbox
TCPPort 1500
HTTPport 1581
SCHEDMODE PROMPTED
MANAGEDSERVICES WEBCLIENT SCHEDULE

3) Create a batch file named sqlfull.cmd under C:\Program Files\Tivoli\TSM\TDPSql\ (there is a sample file sqlfull.smp in the same foder). I want a full backup of the database followed by a log backup every day.

@ECHO OFF
rem  ==================================================================
rem   sqlfull.smp sample command file
rem
rem   Sample command file containing commands to do a scheduled full
rem   backup of all SQL databases to an IBM Tivoli Storage Manager
rem   server.
rem
rem   This file is meant to be executed by the IBM Tivoli Storage
rem   Manager central scheduler in response to a defined schedule on
rem   the IBM Tivoli Storage Manager server.
rem
rem  ==================================================================

rem  ==================================================================
rem   Replace “C:” with the drive where Data Protection for SQL
rem   is installed. Update the directory to match the installation
rem   directory that you chose when you installed the product.
rem  ==================================================================

set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql

C:

cd %sql_dir%

rem  ==================================================================
rem   The two lines below put a date/time stamp in a log file for you.
rem   Note: You can change “sqlsched.log” to whatever you prefer in
rem   lines below.
rem  ==================================================================

date /t < NUL >> %sql_dir%\sqlsched.log
time /t < NUL >> %sql_dir%\sqlsched.log

rem  ==================================================================
rem   Now call the command-line interface to do the backup:
rem
rem   Replace “srvrname” with the name of the options file name you
rem   plan to use.
rem
rem   If SQL authentication is being used and the SQL login settings have
rem   not been stored via the GUI, you must also specify the /sqluser and
rem   /sqlpassword options on the command below.
rem
rem   In this example, we use the ‘*’ to back up all of the databases
rem   on the SQL server. Note that database ‘tempdb’ will not
rem   be backed up.
rem
rem   Note: You can change “sqlsched.log” and “sqlfull.log”  to
rem         whatever you prefer.
rem  ==================================================================

%sql_dir%\tdpsqlc backup * full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log
%sql_dir%\tdpsqlc backup * log /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

set RC=%ERRORLEVEL%
echo ——————— >> %sql_dir%\sqlsched.log
echo Return code was %RC%  >> %sql_dir%\sqlsched.log
echo ===================== >> %sql_dir%\sqlsched.log
exit %RC%

4) Double click the batch file to run a test. Check the sqlsched.log file after it completed.

Thu 14/06/2012
11:56 AM
ANS0102W Unable to open the message repository tdpsena.txt. The American English repository will be used instead.

IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 6, Release 3, Level 0.0
(C) Copyright IBM Corporation 1997, 2011. All rights reserved.

ACO5057I The C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull.log log file was pruned successfully.

Connecting to SQL Server, please wait…

Starting SQL database backup…

Connecting to TSM Server as node ‘TSM_CLIENT01_SQL’…

Beginning full backup for database LcsCDR, 1 of 7.
Full: 3   Read: 6291456  Written: 0  Rate: 0.00 Kb/Sec
Full: 0   Read: 9523968  Written: 9523968  Rate: 4,309.89 Kb/Sec    

Backup of LcsCDR completed successfully.

Beginning full backup for database master, 2 of 7.
Full: 0   Read: 3235584  Written: 3235584  Rate: 7,879.68 Kb/Sec    

Backup of master completed successfully.

Beginning full backup for database model, 3 of 7.
Full: 0   Read: 2184960  Written: 2184960  Rate: 2,243.69 Kb/Sec    

Backup of model completed successfully.

Beginning full backup for database msdb, 4 of 7.
Full: 3   Read: 10485760  Written: 0  Rate: 0.00 Kb/Sec
Full: 0   Read: 12680960  Written: 12680960  Rate: 7,246.20 Kb/Sec    

Backup of msdb completed successfully.

Beginning full backup for database QoEMetrics, 5 of 7.
Full: 0   Read: 5335808  Written: 5335808  Rate: 6,892.53 Kb/Sec    

Backup of QoEMetrics completed successfully.

Beginning full backup for database ReportServer, 6 of 7.
Full: 3   Read: 11534336  Written: 0  Rate: 0.00 Kb/Sec
Full: 3   Read: 20027904  Written: 0  Rate: 0.00 Kb/Sec
Full: 0   Read: 20030208  Written: 20030208  Rate: 8,851.02 Kb/Sec    

Backup of ReportServer completed successfully.

Beginning full backup for database ReportServerTempDB, 7 of 7.
Full: 0   Read: 2190080  Written: 2190080  Rate: 3,839.77 Kb/Sec    

Backup of ReportServerTempDB completed successfully.

Inactivating log backup LcsCDR\201206131534450001628
Inactivating log backup QoEMetrics\201206131629490000AA4

Total SQL backups selected:               7
Total SQL backups attempted:              7
Total SQL backups completed:              7
Total SQL backups excluded:               0
Total SQL backups inactivated:            2
Total SQL backups deduplicated:           0

Throughput rate:                          6,156.55 Kb/Sec
Total bytes inspected:                    55,181,568
Total bytes transferred:                  55,181,568
Total LanFree bytes transferred:          0
Total bytes before deduplication:         0
Total bytes after deduplication:          0
Data compressed by:                       0%
Deduplication reduction:                  0.00%
Total data reduction ratio:               0.00%
Elapsed processing time:                  8.75 Secs

ANS0102W Unable to open the message repository tdpsena.txt. The American English repository will be used instead.

IBM Tivoli Storage Manager for Databases:
Data Protection for Microsoft SQL Server
Version 6, Release 3, Level 0.0
(C) Copyright IBM Corporation 1997, 2011. All rights reserved.

ACO5057I The C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull.log log file was pruned successfully.

Connecting to SQL Server, please wait…

Starting SQL database backup…

Connecting to TSM Server as node ‘TSM_CLIENT01_SQL’…

Beginning log backup for database LcsCDR, 1 of 4.
Full: 0   Read: 84736  Written: 84736  Rate: 57.83 Kb/Sec
Full: 0   Read: 84736  Written: 84736  Rate: 52.34 Kb/Sec    

Backup of LcsCDR completed successfully.

Beginning log backup for database model, 2 of 4.
Full: 0   Read: 85760  Written: 85760  Rate: 98.41 Kb/Sec    

Backup of model completed successfully.

Beginning log backup for database QoEMetrics, 3 of 4.
Full: 0   Read: 90880  Written: 90880  Rate: 98.50 Kb/Sec    

Backup of QoEMetrics completed successfully.

Beginning log backup for database ReportServer, 4 of 4.
Full: 0   Read: 105216  Written: 105216  Rate: 101.53 Kb/Sec
Full: 0   Read: 105216  Written: 105216  Rate: 88.43 Kb/Sec    

Backup of ReportServer completed successfully.

Total SQL backups selected:               7
Total SQL backups attempted:              4
Total SQL backups completed:              4
Total SQL backups excluded:               3
Total SQL backups deduplicated:           0

Throughput rate:                          79.59 Kb/Sec
Total bytes inspected:                    366,592
Total bytes transferred:                  366,592
Total LanFree bytes transferred:          0
Total bytes before deduplication:         0
Total bytes after deduplication:          0
Data compressed by:                       0%
Deduplication reduction:                  0.00%
Total data reduction ratio:               0.00%
Elapsed processing time:                  4.50 Secs

———————
Return code was 0
=====================

5) Login to the TSM_Sandbox to create a SQL backup schedule for this job, and associate TSM_Client01_SQL to this schedule.

def sched SQL Daily_Full desc=”SQL Daily Full Backup” action=command objects=”objects=”C:\Progra~1\Tivoli\TSM\TDPSql\sqlfull.cmd” priority=2 starttime= now duration=15 duru=minutes period=1 perunits=day dayofweek=any
define association SQL Daily_Full TSM_Client01_SQL

6) Check the log file to confirm the job is completed successfully.

or check the event on the TSM_Sandbox by running:

q event SQL Daily_Full f=d

NOTE: If you run the TSM CAD service as the local system account, please ensure the NT authority/system account has the sysadmin role in the SQL server.