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

image

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

image

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

image

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

image

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.

image

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

image

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.

image

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

image

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

image

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

image

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

image

image

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

image

image

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

image

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.

image

or check the event on the TSM_Sandbox by running:

q event SQL Daily_Full f=d

image

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.

Advertisements

27 thoughts on “TSM 6.3: Backup SQL Database

  1. I want to take backup of sql server 2008 which is active/passive cluster so please help me how to configure TDP 6.3 for VSS backup in cluster environment with backup destination is tape library using manually and scheduler

  2. how to backup SQL Database in disk as well tape.
    we have configured two policy domain one for daily and one for weekly. daily backup goes to disk and weekly backup goes to tape so how to configure the same.
    also same how to configure for linux file system

    Best Regards, Diven Shah

  3. thanx for the update. i would also like like to know how to install and configure multiple schedule in Linux node

  4. To enable log truncation in the script, ‘/truncate=yes’ has to be used. For example:

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

  5. Great post on installing and configuring 6.3. Since you can’t truncate the logs unless you run a legacy backup, is the standard methodology to run a VSS snapshot backup once a week and then legacy backups in between? I can’t see how switching to 6.3 will benefit me at the moment. We take a log backups hourly and run legacy full’s every night.

    1. Are you sure ‘you can’t truncate the logs unless you run a legacy backup’?? As far as I know VSS backup can not be restored to an alternate SQL server, and it is why I decided to use legacy backup.

      Add ‘/truncate=yes’ in your SQL VSS backup command file, then test to see if it works. Here is a example:

      %sql_dir%\tdpsqlc backup * log /truncate=yes /tsmoptfile=%sql_dir%\dsm_sql.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

    2. I have confirmed that the VSS backup method is not applicable to LOG backups, only legacy method is. So you have to use legacy backup if you want to backup logs. Use ‘/truncate=yes’ in case you need the logs get truncated.

      1. I read deeper into the documentation and it looks like they recommend combining all three backups:

        Legacy FULL 1+ per week
        VSS FULL 1+ per day
        Legacy DIFF 1+ per day
        Legacy LOG 1+ per day

  6. we have configured sample file for SQL backup now when i run the file the bacth file doesnt runs but same file when i right click and say run as administrator it works fine so wat could be the issue. note the user which i logged on to the system is member of domain admin group.

  7. Estimado Jackie Chen

    Me gustaría puedas comentar sobre las soluciones de backup de vm e integración con vmware que tienen tsm 6.x

    Gracias

  8. Hi Jackie,
    Having a strange issue when installing TDP for SQL, tried 6.3 and 6.4, i just can’t browse SQL databases inside the SQL Management console.
    This is an SQL Express 2008 instance, i can login through SQL management Studio just fine, with the same account i use to install the TDP.
    DO you think it’s because it is an SQL Express instance?
    On the management console when i try to run the config wizard on the SQL it says not installed!!!
    I’m a little bit lost here… any help would be appreciated.
    Thanks

  9. Hi Jackie, following my previous comment… i went on the HW & SW requirements page for TDP SQL and now i know that SQL Express is not a supported.
    Thanks anyway, i guess my client will have to keep 5.5.6 for this kind of nodes.
    Thank you

  10. HI SIR\MAM I M ROHIT SAHU

    i download tdpsql in ibm fix pack site i install tdpsql software but i did not get sqlclient.lic file if u dont mind if u have sqlclient.lic file can u send me my

    mail id :- rohitsh950@gmail.com

    or suggest me where can i download tdpsql and tdpsql.lic file

    Thanks and Regards
    ROHIT KUMAR SAHU

    1. Someone can correct me if I am wrong, but the license file usually comes with the base installer, not a fix pack. You would need 6.3.0.0 for example or copy it from a server with a previous installation of TDP SQL.

  11. Please can you give me the tdpsqlc.exe commands to do the following backups regime:-

    Full back up every Sunday at 1.00 am
    Differential back up every day from Monday to Saturday at 1.00 am
    Transaction log every day at 10.00 am, 12.00 midday, 2 pm, and 4 pm.

  12. I would need to restore a specific date FULL backup of SQL Server Production to UAT. Could you let me know the tdpsqlc.exe command to include the specific date backup ? For example: Would need to restore Jan 1st 2017 backup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s