Results 1 to 1 of 1
09-30-2011, 06:37 AM #1
- Join Date
- Sep 2011
- Thanked 2 Times in 1 Post
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 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
By collinsk in forum ScriptingReplies: 5Last Post: 03-29-2012, 01:06 PM
By Rouen in forum Microsoft SQL ServerReplies: 11Last Post: 07-20-2010, 07:32 AM
By GregE in forum TDP/Application Layer BackupReplies: 5Last Post: 08-20-2008, 02:16 PM
By sgornick in forum TDP/Application Layer BackupReplies: 1Last Post: 07-28-2005, 12:46 PM
By Toha in forum OthersReplies: 2Last Post: 12-20-2004, 04:55 AM