1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

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

Discussion in 'TSM Operation' started by collinsk, Sep 30, 2011.

  1. collinsk

    collinsk New Member

    Sep 30, 2011
    Likes Received:
    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

Share This Page