Results 1 to 6 of 6
  1. #1
    Newcomer
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post

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

    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

  2. The Following 2 Users Say Thank You to collinsk For This Useful Post:

    admin (10-08-2011),microosoft (03-29-2012)

  3. #2
    Member
    Join Date
    Sep 2006
    Location
    India
    Posts
    340
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Default

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

  4. #3
    Newcomer
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post

    Default

    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. #4
    Member
    Join Date
    Oct 2007
    Location
    Australia
    Posts
    92
    Thanks
    0
    Thanked 9 Times in 9 Posts

    Default

    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.

  6. The Following User Says Thank You to sjl For This Useful Post:

    microosoft (03-29-2012)

  7. #5
    Newcomer
    Join Date
    Sep 2011
    Posts
    4
    Thanks
    0
    Thanked 2 Times in 1 Post

    Default

    Really good tip SJL. Thanks

  8. #6
    Newcomer
    Join Date
    Aug 2009
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    This is awesome. Thanks for your help.

Similar Threads

  1. Full TDP SQL Backups versus Log Backups
    By Rouen in forum Microsoft SQL Server
    Replies: 11
    Last Post: 07-20-2010, 07:32 AM
  2. Differential SQL backups on Windows 2000
    By jogolden in forum Microsoft SQL Server
    Replies: 2
    Last Post: 06-17-2009, 01:04 PM
  3. Question on Differential SQL Backups
    By GregE in forum TDP/Application Layer Backup
    Replies: 5
    Last Post: 08-20-2008, 02:16 PM
  4. Replies: 1
    Last Post: 07-28-2005, 12:46 PM
  5. Replies: 2
    Last Post: 12-20-2004, 04:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •