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.txt"

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