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 'Scripting' started by collinsk, Sep 30, 2011.

  1. collinsk

    collinsk New Member

    Joined:
    Sep 30, 2011
    Messages:
    4
    Likes Received:
    2
    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
     
    microosoft and admin like this.
  2.  
  3. Hema

    Hema Member

    Joined:
    Sep 21, 2006
    Messages:
    340
    Likes Received:
    9
    Occupation:
    TSM Backup Consultant
    Location:
    India
    Real good one. I'm gonna try this. Thanks Collinsk.
     
  4. collinsk

    collinsk New Member

    Joined:
    Sep 30, 2011
    Messages:
    4
    Likes Received:
    2
    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
     
  5. sjl

    sjl Member

    Joined:
    Oct 30, 2007
    Messages:
    92
    Likes Received:
    12
    Location:
    Australia
    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:

    Code:
    (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.
     
    microosoft likes this.
  6. collinsk

    collinsk New Member

    Joined:
    Sep 30, 2011
    Messages:
    4
    Likes Received:
    2
    Really good tip SJL. Thanks
     
  7. microosoft

    microosoft New Member

    Joined:
    Aug 23, 2009
    Messages:
    8
    Likes Received:
    0
    This is awesome. Thanks for your help.
     

Share This Page