October 25, 2016 at 11:09 am
Situation: Boss does not like backing up user databases in our non-prod environment. We frequently restore down from Production as well as using non-prod for testing, so the environment is constantly in flux. System DBs and environment specific user DBs (like our admin / metrics databases) are backed up regularly.
Now the issue is being able to recovery user DBs during QA Testing in case something goes fubar. The goal is to write code for our QA team to mark user databases for backup. I've got most of it done, but my sticking point is figuring out if the current version of the database has ever had a FULL backup done before (for reasons of space issues, we're doing one FULL and then differentials). The code I've written is pretty cumbersome and I'm wondering if I'm missing an easier way to do this.
Basically it needs to tell me if there has been a FULL backup of the database done after the last time it was restored. dbo.tUserDBs is the table that the QA team will be updating to ensure specific user DBs will be backed up during their testing period.
CREATE TABLE dbo.tUserDBs (DBName VARCHAR(50), LastDate DATE, YesNo BIT);
INSERT INTO dbo.tUserDBs (DBName, LastDate, YesNo)
VALUES ('MyDB', '11/01/2016',1); --Whatever user DB you want to verify this code with
--And what the last day of the backup cycle will be
--YesNo gets automatically set to 0 (for No) at a certain time frame
WITH EnabledDBs AS
(SELECT bs.database_name, MAX(bs.database_creation_date) AS DBCreationDate
FROM msdb.dbo.backupset bs
INNER JOIN dbo.tUserDBs tdb
ON bs.database_name = tdb.DBName
WHERE tdb.YesNo = 1 AND tdb.LastDate >= CONVERT(DATE,GETDATE())
AND bs.is_snapshot = 0 AND bs.is_copy_only = 0 AND bs.type = 'D'
GROUP BY database_name),
LastFullBackup AS
(SELECT bs1.database_name, edb.DBCreationDate, MAX(bs1.backup_start_date) AS LastBackupStartDate
FROM msdb.dbo.backupset bs1
INNER JOIN EnabledDBs edb
ON bs1.database_name = edb.database_name
AND bs1.database_creation_date = edb.DBCreationDate
WHERE bs1.is_snapshot = 0 AND bs1.is_copy_only = 0 AND bs1.type = 'D'
GROUP BY bs1.database_name, edb.DBCreationDate),
LastRestore AS
(SELECT TOP 1 restore_date, destination_database_name
FROM msdb.dbo.restorehistory
WHERE recovery = 1
ORDER BY restore_date DESC) -- database was last restored
SELECT bs2.database_name--, bs2.database_creation_date, bs2.backup_start_date, bs2.first_lsn, bs2.last_lsn, bmf.physical_device_name, --bs2.*
FROM msdb.dbo.backupset bs2
INNER JOIN LastFullBackup lfb
ON bs2.database_name = lfb.database_name
AND bs2.database_creation_date = lfb.DBCreationDate
AND bs2.backup_start_date = lfb.LastBackupStartDate
INNER JOIN msdb.dbo.backupmediaset bms
ON bs2.media_set_id = bms.media_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs2.media_set_id = bmf.media_set_id
INNER JOIN LastRestore lr
ON bs2.database_name = lr.destination_database_name
WHERE bs2.backup_start_date >= lr.restore_date;
I feel like I'm over-engineering this database backup check. Is this the best way to do this check?
October 25, 2016 at 11:33 am
Just off the top of my head, I'd do something like below ... if no backup date appears, then there was no full backup after the last restore.
SELECT tdb.*, rh.restore_date AS Last_Restore, bs2.backup_finish_date AS Backup_After_Restore
FROM dbo.tUserDBs tdb
OUTER APPLY (
SELECT TOP (1) *
FROM msdb.dbo.restorehistory rh2
WHERE
rh2.destination_database_name = tdb.DBName AND
(rh2.restore_type IS NULL OR rh2.restore_type <> 'V')
ORDER BY restore_history_id DESC /*presumably accurate rather than datetime*/
) AS rh
OUTER APPLY (
SELECT TOP (1) *
FROM msdb.dbo.backupset bs2
WHERE
bs2.database_name = tdb.DBName AND
bs2.is_copy_only = 0 AND
bs2.is_snapshot = 0 AND
bs2.type = 'D' AND
bs2.backup_finish_date > rh.restore_date
ORDER BY backup_set_id DESC /*presumably accurate vs datetime*/
) AS bs2
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 26, 2016 at 6:25 am
Huh. That looks interesting. Thank you. I'll give it a shot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply