March 23, 2010 at 8:46 am
I need to create a backup report having databases which are not backed up since more than 1 day.
Here is the case:
I am deploying script on the Server ABC. The msdb..backupset table was supposed to have entries related to machine_name and server_name ABC.
But When i checked entries in the msdb..backupset table I found one entry with server_name and machine_name XYZ. I was surprised to have such entry.
Can somebody tell me in which case (how) backupset table have an entry which will contain the server_name and machine_name(XYZ) other than a Server on which msdb lies(ABC)??
March 23, 2010 at 8:56 am
Is ABC set up as a master server? Or maybe msdb has at some point been restored from XYZ?
John
March 23, 2010 at 9:04 am
log shipping updates the backupset table of the secondary server with backup information.
mirroring might too, but not sure about that, I can check tomorrow unless someone knows for sure.
---------------------------------------------------------------------
March 23, 2010 at 9:09 am
george sibbald (3/23/2010)
log shipping updates the backupset table of the secondary server with backup information.mirroring might too, but not sure about that, I can check tomorrow unless someone knows for sure.
George
Yes, we have entries for our primary server in the backupset table on the mirror server.
John
March 23, 2010 at 9:13 am
cheers John, I thought it would, seems sensible thing to do as roles can change.
---------------------------------------------------------------------
March 23, 2010 at 9:28 am
hi. i just figured out the reason in my case.
There was entry for database DB1. The database DB1 was restored from XYZ server to ABC, and because of that there was entry in the backupset table in msdb of ABC.
idea struck to me when i read about mirroring post. I restored DB1 of XYZ on ABC and checked backupset entries. It worked !!!!!!!!!
Thanks for your quick reply.
March 23, 2010 at 10:06 am
Restore operations have entires in the msdb.dbo.restorehistory table so you can filter them out of the backupset table based on a join on backup_set_id.
The SQL snippet below is something that I must have put together at some point to show me, by database, what the latest full, differential and transaction log backups were.
Might be of some use to you.
SELECT dbs.[name] AS [Database],
MAX(CASE WHEN bkp.[Type] = 'D' THEN bkp.[backup_start_date] ELSE NULL END) AS [LastFullBackup],
MAX(CASE WHEN bkp.[Type] = 'I' THEN bkp.[backup_start_date] ELSE NULL END) AS [LastDiffBackup],
MAX(CASE WHEN bkp.[Type] = 'L' THEN bkp.[backup_start_date] ELSE NULL END) AS [LastLogBackup]
FROM [master].[dbo].[sysdatabases] dbs
LEFT JOIN [msdb].[dbo].[backupset] bkp
ON dbs.[name] = bkp.[database_name]
AND bkp.[backup_start_date] >= dbs.[crdate]
AND NOT EXISTS ( SELECT 1
FROM msdb.dbo.[restorehistory] rst
WHERE rst.[backup_set_id] = bkp.[backup_set_id])
GROUP BY dbs.[name]
ORDER BY dbs.[name]
Notice the "NOT EXISTS" to filter out restores. Also note the check for backup_start_date >= crdate. If you backup a database then drop it and then create it again (from a different source), you might not want to pick up old backups assuming they will help you recover the newly created version.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply