October 27, 2015 at 1:39 pm
I've got the below and have several variation and still cant seem to find a perfect way to query the server to bring back that last full backup per db. I'm shopwing mutilple records in the backup set db w/ type = 'D'. I look online and type D = Database. Which i assumed it meant full database backup. Apparently not. Try running the below on one of your full databases. Then check to see if the date is actually the last backup date.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,
Cast(Cast(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
+ ' ' + 'MB' AS bksize,
Cast(Datediff(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4))
+ ' ' + 'Seconds' timetaken,
s.backup_start_date,
Cast(s.first_lsn AS VARCHAR(50)) AS first_lsn,
Cast(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS backuptype,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
and s.[type]='d'
ORDER BY backup_start_date DESC,
backup_finish_date
October 27, 2015 at 2:27 pm
where .....
and is_snapshot=0
October 28, 2015 at 5:08 am
Snargables (10/27/2015)
A database name is a data type SYSNAME or VARCHAR(128), declare it as so or you may have issues if you have databases with really long names.
Snargables (10/27/2015)
SELECT @db_name = DB_NAME()-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,
Cast(Cast(s.backup_size / 1000000 AS INT) AS VARCHAR(14))
+ ' ' + 'MB' AS bksize,
Cast(Datediff(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4))
+ ' ' + 'Seconds' timetaken,
s.backup_start_date,
Cast(s.first_lsn AS VARCHAR(50)) AS first_lsn,
Cast(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS backuptype,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
and s.[type]='d'
ORDER BY backup_start_date DESC,
backup_finish_date
Why specify a case on type when your predicate specifies type 'D'
This query maybe a little closer to what you actually require
SELECTbs.database_name
, bs.backup_start_date
, bs.backup_finish_date
, bs.backup_size / 1048576 AS SizeMBs
, bs.compressed_backup_size / 1048576 AS CompressedSizeMBs
, bmf.physical_device_name
, bs.user_name
, bs.database_backup_lsn
, bs.server_name
FROM msdb.dbo.backupset bs INNER JOIN
msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
WHERE type = 'D' AND database_name
NOT IN ('master', 'model','msdb','tempdb')
ORDER BY bs.backup_finish_date, bs.database_name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 28, 2015 at 5:34 am
If you want the last full backup for each database, this should get you there. You can add in the other columns you need.
WITH cteFullBackups AS (
SELECT bs.database_name, bs.type, bs.backup_finish_date, bmf.physical_device_name, bs.backup_size / POWER(1024, 2) SizeMB,
backup_number = ROW_NUMBER() OVER(PARTITION BY bs.database_name ORDER BY bs.backup_finish_date DESC)
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.type = 'D'
)
SELECT *
FROM cteFullBackups
WHERE backup_number = 1
ORDER BY database_name;
October 28, 2015 at 7:21 am
I'm not trying to be rude here however none of these work properly and are returning extraneous results. If u run it right after the full backup it returns the correct results however I run it a day later it is picking up on some other record in the backupset table that has a type of D. I'm attaching a spreadsheet of the backup set records w/ Type = 'D'. the ones highlighted in yellow are the actual confirmed full backups. I don’t know why the other records are getting loaded in there. I verified they do not actually map to a physical backup nor I can find a way to omit them from my query. This is happening in all of my sql instances by the way from 2008 r2 through 2014. I don’t know how this isn't an issue for others who are trying to send out alerts based on if a backup never ran on a myriad on instances and db's
October 28, 2015 at 7:34 am
Looks like you have a 3rd party tool also backup up the DB's as the is_snapshot flag is set to 1.
You can filter them out by adding
WHERE msdb.dbo.backupset.is_snapshot = 0
October 28, 2015 at 8:15 am
Actually, ur right. On the two servers i spot checked. One was doing a backup restore to our QA environment which explained the extra record. The other, which is the one i attached. It appears our server team took it upon themselves to do backups daily using activio. So we have a sql maintinence plan doing a full weekly, diff, daily and activio backing them up daily. Question, If we're doing sql backup plan and activio, wont that affect the chain for point in time recovery for the sql backups.
October 28, 2015 at 8:22 am
Generally yes it would as a full backup resets the differential point.
But as these are snapshot backups using VSS unsure how they interact with the differential markers.
Would be worth doing a test restore of your native full and a native diff taken after an Activio full.
Just noticed on your export that your maint plan is doing a copy only backup, a copy only cannot serve as a diff base, so you may not have a full chain of backups if you no longer have the original none copy only full backup
Maintenance plans are ok to give you a starting point, but they are heavily static in what they can/cannot do, have you looked at Ola Hallengren's maintenance scripts, or even thought about writing you own?
October 28, 2015 at 8:24 am
Snargables (10/28/2015)
Actually, ur right. On the two servers i spot checked. One was doing a backup restore to our QA environment which explained the extra record. The other, which is the one i attached. It appears our server team took it upon themselves to do backups daily using activio. So we have a sql maintinence plan doing a full weekly, diff, daily and activio backing them up daily. Question, If we're doing sql backup plan and activio, wont that affect the chain for point in time recovery for the sql backups.
Having asked this before myself, what the server team full backups will affect is the differential backups. So your daily Diffs will be based from the daily Activio, NOT your weekly full as you might expect. Presuming you're doing transaction log backups as well, as long as the chain is unbroken, you could restore from your weekly full to any point-in-time during the week (presuming nothing else, such as a DB getting flipped to simple or bulk-logged recovery and back to full)
October 28, 2015 at 8:26 am
Snargables (10/28/2015)
Actually, ur right. On the two servers i spot checked. One was doing a backup restore to our QA environment which explained the extra record. The other, which is the one i attached. It appears our server team took it upon themselves to do backups daily using activio. So we have a sql maintinence plan doing a full weekly, diff, daily and activio backing them up daily. Question, If we're doing sql backup plan and activio, wont that affect the chain for point in time recovery for the sql backups.
I had a similar situation where our network team was running Backup Exec twice a week and running the full backups somewhere I couldn't access. It made things a bit more complicated when I did a restore. I eventually convinced them to stop, which made things much simpler.
Now, SQL Server does its own native backups and the network team backs up the backups. See if they're willing to go in that direction.
October 28, 2015 at 9:32 am
Snargables (10/28/2015)
I'm not trying to be rude here however none of these work properly and are returning extraneous results.
There's nothing wrong with either of the queries supplied above, the issue is your system you're running it on
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply