Annoyed with TDP SQL differential backups failing because no full backup exists?

[collinsk]

Ever been annoyed because your scheduled TDP SQL differential backup fails because someone has added a new database and not taken a full backup?

After extensive searching I was unable to find anyone who had documented a solution so I took to writing a script to check if a full backup of a database exists before taking a differential backup. Turns out the scripting was relatively straight forward.

Below is a copy of the script I now use for scheduled differential backups. Hope some of you find it of use.


REM Set Environment Variables
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
set sql_log=C:\Progra~1\Tivoli\TSM\logs\sql
set backupcheck="C:\Progra~1\Tivoli\TSM\TDPSql\check.t xt"

REM Write Date & Time To Log File
echo %date% %time% >> %sql_log%\sqlsched.log

REM Output Databases With No Backups
sqlcmd -E -S %computername% -d master -Q "SET NOCOUNT ON SELECT master.dbo.sysdatabases.NAME AS database_name, NULL AS [Last Data Backup Date], 9999 AS [Backup Age (Hours)] FROM master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' ORDER BY msdb.dbo.backupset.database_name" -h-1 >%backupcheck%

REM Backup Databases With No Backups
for /f "tokens=1" %%i in ('type %backupcheck%') do %sql_dir%\tdpsqlc backup %%i FULL /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_log%\sqlfull.log >> %sql_log%\sqlsched.log

REM Diff Backup Of All Databases
%sql_dir%\tdpsqlc backup * diff /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_log%\sqldiff.log >> %sql_log%\sqlsched.log

[Hema]

Real good one. I'm gonna try this. Thanks Collinsk.

[collinsk]

Quick Update:-

If you want to run this against SQL 2000 you will need to install the SQL Command Line Utilities which you can download from here:-
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3522

The script also needs a slight tweak by adding an asterix in the full backup section:-

REM Backup Databases With No Backups
for /f "tokens=1" %%i in ('type %backupcheck%') do %sql_dir%tdpsqlc backup %%i* FULL /tsmoptfile=%sql_dir%dsm.opt /logfile=%sql_log%sqlfull.log >> %sql_log%sqlsched.log

[sjl]

Another handy tip, while we're on the subject of TDP backups and scripting thereof.

If you're running multiple TDP backup commands in a single Windows command script (or even just if the TDP command isn't the last command in the script), I'd recommend using a structure similar to this:

(script initialisation code)
set retval=0
(more script initialisation code)
(random non-backup commands)
TDP command #1 goes here
if errorlevel 1 set retval=%errorlevel%
(more random non-backup commands)
TDP command #2 goes here
if errorlevel 1 set retval=%errorlevel%
(more random non-backup commands)
TDP command #3 goes here
if errorlevel 1 set retval=%errorlevel%
(repeat ad nauseum)
(closing up code)
exit /b %retval%

What this does is it checks to see whether the TDP call indicated a failure of some sort; if it did, it sets the retval variable to the return code. You're not guaranteed to get the highest return code, but you are guaranteed to get a non-zero code if at least one TDP call failed - and that that non-zero code will be returned to the TSM scheduler. That, in turn, will cause the job to be marked as failed. Makes life a lot easier than trawling through logs to try to find out if anything failed, or blindly assuming that everything's happy.

I won't bother giving the Unix equivalent here; I'm assuming that most TSM admins are competent at basic Unix shell scripting.

[collinsk]

Really good tip SJL. Thanks

[microosoft]

This is awesome. Thanks for your help.