November 2, 2023 at 7:14 am
Hi All,
I am looking for query to get latest full backup with all database except tempDB. I have got query from internet and trying to make changes looks like some databases are missing from sys.databases.
I wanted to do daily check of last full, Differential and Log backup. if I get query for full backup, I can change the type ='D' to I or L for others.
Thanks for the help.
-- Full backup
WITH cte AS (
SELECT @@SERVERNAME AS ServerName,
d.[name] AS DatabaseName,
F.physical_device_name,
d.recovery_model_desc,
b.user_name as UserName,
'Last backed up: ' + COALESCE(CAST(MAX(b.backup_finish_date) AS VARCHAR(25)),'never') AS [Backup Details],
ROW_NUMBER() OVER (PARTITION BY d.[name] ORDER BY MAX(b.backup_finish_date) DESC) rn
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b
ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
left outer join MSDB.DBO.BACKUPMEDIAFAMILY F ON (f.media_set_id=b.media_set_id)
AND b.type = 'D' -- Full
AND b.server_name = SERVERPROPERTY('ServerName')
WHERE d.database_id not in (2) -- exclude tempdb
--AND d.state NOT IN(1, 6, 10)
AND d.is_in_standby = 0
AND d.source_database_id IS NULL
--and F.physical_device_name not like '{%'
GROUP BY d.name,
b.user_name,recovery_model_desc,F.physical_device_name
--HAVING MAX(b.backup_finish_date) <= DATEADD(dd, -7, GETDATE()) OR MAX(b.backup_finish_date) IS NULL
)
SELECT *
FROM cte t
WHERE t.rn = 1
November 2, 2023 at 8:48 am
You should also perform restores on a regular basis to see of your backups still meet your DRP requirements!
( receiving succeeded reports on a daily basis makes people used to it and neglect it ( or maybe even filter it to "delete on receive" ), so missing your objective to put this all in place )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 2, 2023 at 1:47 pm
;WITH cte AS (
SELECT @@SERVERNAME AS ServerName,
b.database_name AS DatabaseName,
F.physical_device_name,
b.recovery_model,
b.user_name as UserName,
b.backup_finish_date,
ROW_NUMBER() OVER (PARTITION BY b.database_name ORDER BY b.backup_finish_date DESC) rn
FROM msdb.dbo.backupset b
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY F ON (f.media_set_id=b.media_set_id)
LEFT OUTER JOIN master.sys.databases d
ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE b.type = 'D' -- Full
AND b.server_name = SERVERPROPERTY('ServerName')
AND b.database_name NOT IN ('tempdb')
--AND d.state NOT IN(1, 6, 10)
)
SELECT ServerName, DatabaseName, physical_device_name, recovery_model, UserName,
'Last backed up: ' + COALESCE(CAST(backup_finish_date AS VARCHAR(25)),'never') AS [Backup Details]
FROM cte t
WHERE t.rn = 1
ORDER BY DatabaseName
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 2, 2023 at 2:20 pm
Hi ScottPletcher,
Thanks for the code. It is really helpful. I can see if the database does not have any backup after it is created is not showing. Anyway I will make one backup.
November 2, 2023 at 3:35 pm
Hi ScottPletcher,
Thanks for the code. It is really helpful. I can see if the database does not have any backup after it is created is not showing. Anyway I will make one backup.
This where I got stuck. Some databases in full recovery mode and that does not have any log backup happen so for from the DB creation. Those are all not listed from the code, I think because of joins.
November 2, 2023 at 5:20 pm
;WITH cte AS (
SELECT @@SERVERNAME AS ServerName,
ISNULL(b.database_name, d.name) AS DatabaseName,
F.physical_device_name,
ISNULL(b.recovery_model, d.recovery_model_desc) AS recovery_model,
b.user_name as UserName,
b.backup_finish_date,
ROW_NUMBER() OVER (PARTITION BY ISNULL(b.database_name, d.name) ORDER BY b.backup_finish_date DESC) rn
FROM msdb.dbo.backupset b
FULL OUTER JOIN master.sys.databases d
ON d.name COLLATE SQL_Latin1_General_CP1_CI_AS = b.database_name COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN MSDB.DBO.BACKUPMEDIAFAMILY F ON (f.media_set_id=b.media_set_id)
WHERE b.type = 'D' -- Full
AND b.backup_finish_date >= DATEADD(DAY, -7, CAST(GETDATE() AS date))
AND b.server_name = SERVERPROPERTY('ServerName')
AND b.database_name NOT IN ('tempdb')
--AND d.state NOT IN(1, 6, 10)
)
SELECT ServerName, DatabaseName, physical_device_name, recovery_model, UserName,
'Last backed up: ' + COALESCE(CAST(backup_finish_date AS VARCHAR(25)),'never') AS [Backup Details]
FROM cte t
WHERE t.rn = 1
ORDER BY DatabaseName
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 2, 2023 at 10:58 pm
Daily checks are a great idea - you are on the right track. You don't want to rely on email alerts alone - it's best to validate that things are in a good state. You won't get a failed backup notification if your backup never runs or a database is skipped for some reason. Also, you might just not receive the notification for some reason (email rules, spam filters etc).
I created a monitoring tool, DBA Dash that can help with your daily checks. It will provide you with a dashboard so you can see at a glance if you are missing backups across all your SQL instances. It also includes many other things that you might want to include in your daily check process. Thresholds and exclusions are configurable. It also does performance monitoring and a lot of other things you might find useful.
DBA Dash is free and open source. If you just want a script, this is the one I use for DBA Dash to get the last backup of each type which might give you a starting point.
Hope this helps
DBA Dash - Free, open source monitoring for SQL Server
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply