Veritas Diff/Full Backups

Question

Post reply

Veritas Diff/Full Backups

wade ungaro

SSC Veteran

Points: 290
More actions
August 10, 2004 at 5:43 pm

#109027

Alright, here is my scenario for my backups on a few servers:
Weekly (Thursday) - Full backup to tape through Veritas NetBackup (using SQL Agent)
Daily - Differential backup to tape through Veritas NetBackup (using SQL Agent)
Every 2 Hours - Transaction Log backups using SQL Maintenance Plan (for now, will switch to Veritas soon)
Given, using Veritas is our only option right now (pressured by the higher ups) instead of going to disk using SQL Maintenance Plans and then to tape, how can you verify from within SQL Enterprise Manager that the Differential backup ran successfully. When we run a Full (or when using SQL Maintenance Plans on other servers) I can run the following query:
SELECT @@servername as Server_Name,
B.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(a.backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(a.backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset a
ON a.database_name = B.name AND a.type = 'D'
where (B.name not like 'Northwind%'
and B.name not like 'tempdb%'
and B.name not like 'pubs%')
GROUP BY B.name
ORDER BY B.name
This will give me the Server_Name, Database_Name, DaysSinceLastBackup, and LastBackupDate. This query runs in a DTS for each server and then spits out a huge .html file for morning review.
The problem is when only Differentials are run, the DaysSinceLastBackup never changes. Does anyone know what table to query on to verify the Diff backup ran? I know I can look at the emailed Excel spreadsheet from Veritas every morning, but I would like to keep it the same as it is currently and not have to look in multiple places every day.
Any help is greatly appreciated! TIA
out,
swaders

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wade ungaro SSC Veteran Points: 290 More actions · Answer 1

wade ungaro

SSC Veteran

Points: 290

August 11, 2004 at 11:51 am

#518553

Anyone?!?!?

out,

swaders

vincent SSC Enthusiast Points: 100 More actions · Answer 2

Hi

You need to use the column "type" in table msdb..backupset to differentiate full and diff backup. Pls refer BOL about this column

vincent SSC Enthusiast Points: 100 More actions · Answer 3

I am in the same situation to use Veritas instead of going to disk using SQL Maintenance Plans and then to tape.

But I am not familiar with Veritas very much, I am worry about the restore test part. currently I manually restore to sql server to test it. This morning I hit error message which I don't understand when I try to restore a database form a backup.

Do you have your restore test plan? have you ever hit problem when you restore from backup performed by veritas?

wade ungaro SSC Veteran Points: 290 More actions · Answer 4

Vincent,

Thanks for both replies. The "type" column was just what I needed. I was only looking for 'D' (Full), but needed to look for 'I' (Incremental) also. My query has been fixed.

As far as the testing. I'm currently working on that right now. I can get the Full restore to work using the NetBackup Client, but can't restore the TLogs. I'm having trouble viewing the backup sets to restore from using the NetBackup SQL Agent. My NT guys are working on the security right now. I can't restore the databases from with SQL anymore either. I'm getting "can't open file" errors when trying to use the NetBackup sets. If I can use the SQL Agent I should have no problems restoring in the future.

out,

swaders