• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above 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 notice will disappear after you have made at least 3 posts.

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

#1
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
 
#3
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

ADSM.ORG Senior Member
#4
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.
 

ravikanth

ADSM.ORG Member
#8
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
Hello,

I tried the above script, however it didn't picked the DBs which require full backup. We are using TDP SQL ver7.1.3 and MS SQL2014 server version. Please advice.

Thanks & Regards,
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 17 19.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 53 60.9%
  • Let's be formal and just say Spectrum Protect

    Votes: 10 11.5%
  • Other (please comement)

    Votes: 7 8.0%

Forum statistics

Threads
31,466
Messages
134,102
Members
21,565
Latest member
Chrescht
Top