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
Thanks for sharing this. Have you had to use this on SQL servers where the instances use MSCS clustering? Combining the instance names from registery with the local computername doens’t seem to work. I’m trying to think of a way to create the names from e.g. “cluster res” output.
Unfortunately I didn’t have any clusters as the time. You could probably try to find a way on your own and adapt the script!
Great script. Glad I found this. I plan to link to this script if you don’t mind?
Chad Small
http://www.TSMAdmin.com