Tivoli Storage Manager and SQL backup

The script that was used as a template for initiating the SQL backup from the TDP agent is simply a bat-file. It isn’t very complicated and quite basic. Since deploying this backup within a datacenter and dealing with a few different SQL-servers installed in quite a few different ways – it seemed that this script had as many variations as we had SQL-servers installed. The main flaws were around the fact that multiple SQL instances weren’t detected and that the transaction-log weren’t truncated when the backup had been run for databases that were set to recovery mode FULL. Inorder to make this more manageable the following improvements were made;

  • Detect all SQL instances via the local the registry – based on Jay’s blog
  • Exclude Windows Internal Databases
  • Run through all databases in each instance. Verify if there are any offline instances and databases
  • Backup all databases or each database that are online.
  • Run a backup of transaction log and truncate it – some things based on SpaghettiDBA
  • Track all errors and exit the command with the error if one occurs to allow TSM to report a failed backup

It does not;

  • Detect if a SQL instance is offline
  • Detect if SQL is not installed and then fail

Why a bat-file?
All servers we are supporting doesn’t have Powershell. A reality…

@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 TSMERROR=0

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 ==================================================================

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

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

rem Query all Microsoft SQL Server instances installed

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

ECHO Creating tdpsqlservers.txt > command.log

reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL">> tdpsqlservers.txt

reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL" >> tdpsqlservers.txt

ECHO Generated tdpsqlservers.txt >> command.log

FOR /F "tokens=1 delims= " %%B IN (tdpsqlservers.txt) DO IF NOT "%%B"=="HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft" IF NOT "%%B"=="HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft" IF NOT "%%B"=="MICROSOFT##SSEE" CALL:servers %%B

del tdpsqlservers.txt

ECHO Deleted tdpsqlservers.txt >> command.log

del tdpsql_input.txt

ECHO Deleted tdpsql_input.txt >> command.log

ECHO Exit code %TSMERROR% >> command.log

exit /b %TSMERROR%

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

rem Verify all database instances

rem Check if there are any offline databases

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

:servers

IF NOT "%1"=="MSSQLSERVER" SET INSTANCE=%COMPUTERNAME%\%1

IF NOT "%1"=="MSSQLSERVER" SC QUERY "MSSQL$%1" | find /i "RUNNING"

IF ERRORLEVEL 1 ECHO %INSTANCE% offline >> command.log | GOTO :EOF

IF "%1"=="MSSQLSERVER" SET INSTANCE=%COMPUTERNAME%

IF "%1"=="MSSQLSERVER" SC QUERY "MSSQLSERVER" | find /i "RUNNING"

IF ERRORLEVEL 1 ECHO %INSTANCE% offline >> command.log

IF ERRORLEVEL 1 GOTO :EOF

ECHO Verify Server %INSTANCE% >> command.log

SQLCMD -S %INSTANCE% -E -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE state_desc IN ('OFFLINE')" -h -1 -o tdpsql_offline.txt

ECHO Generated tdpsql_offline.txt >> command.log

SET FILE=tdpsql_offline.txt

FOR %%R IN (%FILE%) DO (

IF %%~zR LSS 87 (GOTO :backup

) ELSE (GOTO :backupoffline)

)

GOTO :EOF

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

rem Run a backup against each instance without offline dbs

rem For each instance - check what databases have FULL recovery

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

:backup

ECHO Backup all databases >> command.log

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

IF NOT "%ERRORLEVEL%"=="0" SET TSMERROR=%ERRORLEVEL%

SQLCMD -S %INSTANCE% -E -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE recovery_model_desc IN ('FULL') AND state_desc IN ('ONLINE')" -W -h -1 -o tdpsql_input.txt

ECHO Generated tdpsql_input.txt >> command.log

FOR /F "delims=" %%A IN (tdpsql_input.txt) DO CALL:translog "%%A"

del tdpsql_offline.txt

ECHO Deleted tdpsql_offline.txt >> command.log

GOTO :EOF

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

rem Run a backup against each instance with offline dbs

rem For each instance - check what databases have FULL recovery

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

:backupoffline

echo Backup - offline DBS located >> command.log

SQLCMD -S %INSTANCE% -E -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE state_desc IN ('ONLINE')" -h -1 -o tdpsql_online.txt

ECHO Generated tdpsql_online.txt >> command.log

FOR /F %%D IN (tdpsql_online.txt) DO CALL:dbbackup %%D

del tdpsql_offline.txt

ECHO Deleted tdpsql_offline.txt >> command.log

del tdpsql_online.txt

ECHO Deleted tdpsql_online.txt >> command.log

SQLCMD -S %INSTANCE% -E -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE recovery_model_desc IN ('FULL') AND state_desc IN ('ONLINE')" -h -1 -o tdpsql_input.txt

FOR /F %%A IN (tdpsql_input.txt) DO CALL:translog %%A

GOTO :EOF

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

rem Run a backup against a specific database

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

:dbbackup

ECHO Backup against specific database %1 >> command.log

CALL %sql_dir%\tdpsqlc backup %1 full /SQLSERVER=%INSTANCE% /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

IF NOT "%ERRORLEVEL%"=="0" SET TSMERROR=%ERRORLEVEL%

GOTO :EOF

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

rem Run a log-file backup and truncate the transaction log

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

:translog

SET DB=%1

ECHO Transaction Log for %DB% >> command.log

CALL %sql_dir%\tdpsqlc backup %1 log /truncate=yes /SQLSERVER=%INSTANCE% /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log

IF NOT "%ERRORLEVEL%"=="0" SET TSMERROR=%ERRORLEVEL%

GOTO :EOF

:EOF

TSM 6.2 Query–Time Calculation

Apparently they redid a couple of things with IBM TSM 6.2 – which allowed a good half of a day to understand the DB2 query language and howto manipulate it.

Apart from the node-name (which should be fairly basic) – the following is the end-result for my finalized query which shows the elapsed time of a job (backups in this specific case) – and providing it in a D HH:MM: SS format. Its so much easier to read.

image

SELECT entity AS "Node name", 
CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer",
SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", 
SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time",
DAY(end_time-start_time) || ' ' || HOUR(end_time-start_time) || ':' || MINUTE(end_time-start_time) || ':' || substr(DIGITS(SECOND(end_time-start_time))8,10) AS "ELAPSED TIME"
 FROM summary 
WHERE activity='BACKUP' 
AND start_time&amp;gt;=current_timestamp - 24 hours GROUP BY end_time, start_time,entity