Configure the scripts to backup your SQL Server

It is necessary to create the scripts on your SQL Server machine. The Data Protection for SQL installation directory includes a sample command file to perform a scheduled full legacy backup of all the SQL databases to the IBM Tivoli Storage Manager server. The file is called sqlfull.smp, as shown in Example 6-3 on page 175. So, you just need to customize it with the appropriate value for you environment, and create all types of backups that you need.

Example 6-3 C:\Program files\tivoli\tsm\tdpsql\sqlfull.smp

@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.

Note: You must specify COMPLETE PATHNAMES in the command file for all file names and non-systems commands.

Note: If you are in a cluster environment, your command file can reside on local drives, but you need to remember to replicate the same command file to all machines in the cluster.

Alternatively, you can create the command file on a shared drive. The TSMOPTFILE and LOGFILE options specified in your command file must reflect the location of the options file and log file on the SQL Server File Share.

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 < NUL >> %sql_dir%\sqlsched.log time < 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%\srvrname.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

Based on this sample script, you can create another script to backup all databases on your server. See Example 6-4 on page 176 for an example.

Example 6-4 Script sql_backupfull.bat

rem Script to backup the database all databases

rem ==================================================================

rem ==================================================================

rem Set the installation directory

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.

%sql_dir%\tdpsqlc backup * full /SQLSERVER=CLUSQL01\SQL01 /backupdestination=TSM /tsmoptfile=%sql_dir%\dsm_sql_daily_sql01.opt

/configfile=%sql_dir%\tdpsql_daily_sql01.cfg

/logfile=%sql_dir%\sqlfull_full_daily.log >> %sql_dir%\sqlsched.log

You can create another script to backup only one database. For example, the script

sql_backupdb.bat was created to backup just the dbsales7. If you intend to backup more than one database you can separate the databases using commas. See Example 6-5 on

page 177.

Example 6-5 Script sql_backupdb.bat

@ECHO OFF

rem ==================================================================

rem Script to backup the database DBSales7

rem ==================================================================

rem ==================================================================

rem Set the installation directory

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.

%sql_dir%\tdpsqlc backup DBSales7 full /SQLSERVER=CLUSQL01\SQL01 /backupdestination=TSM /tsmoptfile=%sql_dir%\dsm_sql_daily_sql01.opt /configfile=%sql_dir%\tdpsql_daily_sql01.cfg

/logfile=%sql_dir%\sqlfull_db_daily.log >> %sql_dir%\sqlsched.log

You can create another script to backup using VSS. For example, the script sql_vss.bat was created to backup all database files. See Example 6-6 on page 178.

Example 6-6 Script sql_vss.bat

@ECHO OFF

rem ==================================================================

rem Script to backup all SQL databases using VSS

rem ==================================================================

rem ==================================================================

rem Set the installation directory

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.

%sql_dir%\tdpsqlc backup * full /SQLSERVER=CLUSQL01\SQL01 /backupdestination=LOCAL /backupmethod=VSS /tsmoptfile=%sql_dir%\dsm_sql_daily_sql01_vss.opt

/configfile=%sql_dir%\tdpsql_daily_sql01.cfg

/logfile=%sql_dir%\sqlfull_db_daily.log >> %sql_dir%\sqlsched_vss.log

You can create another script to backup just the databases logs. For example, the script sql_backuplog.bat, as shown in Example 6-7 on page 179.

Example 6-7 Script backup_sql_logs.bat

@ECHO OFF

rem ==================================================================

rem Script to backup the database SQL Logs

rem ==================================================================

rem ==================================================================

rem Set the installation directory

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.

%sql_dir%\tdpsqlc backup * full /SQLSERVER=CLUSQL01\SQL01 /backupdestination=TSM /tsmoptfile=%sql_dir%\dsm_sql_daily_sql01.opt

/configfile=%sql_dir%\tdpsql_daily_sql01.cfg

/logfile=%sql_dir%\sqlfull_log_daily.log >> %sql_dir%\sqlsched.log