@Grant Fletcher and @Michael L John.
As suggested I looked up the backupset tables and I think we can still
come up with schedules and timings for all databases and their respective
kind of backups if we can use most recent sample data (last 30 days)
assuming there is only one process(sql or 3rd party) tool which does backups.
And where can I find the information regarding which tool is doing backups
- sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
which says even if a 3rd party does backups sometimes the msdb tables
can still carry sqlserver to be the one doing backups.
My query gives me data in this format from the msdb backup tables.
(sample data from last 30 days)
MyServerAMyDB10/24/20 1:00 AMD:\ABC.bakFULL
MyServerAMyDB10/17/20 1:00 AMD:\ABC.bakFULL
MyServerAMyDB10/10/20 1:00 AMD:\ABC.bakFULL
MyServerAMyDB10/3/20 1:00 AM D:\ABC.bakFULL
MyServerAMyDB10/23/20 1:00 AMD:\ABC.bakDIFF
MyServerAMyDB10/22/20 1:00 AMD:\ABC.bakDIFF
MyServerAMyDB10/21/20 1:00 AMD:\ABC.bakDIFF
MyServerAMyDB10/20/20 1:00 AM D:\ABC.bakDIFF
MyServerAMyDB10/19/20 1:00 AMD:\ABC.bakDIFF
MyServerAMyDB10/18/20 1:00 AM D:\ABC.bakDIFF
MyServerAMyDB10/16/20 1:00 AMD:\ABC.bakDIFF
Full backups on Saturdays-weekly once
Diffs all days but on saturdays
And tlogs every 4 hours every day
How can I use the above data form the backup tables in msdb and
conclude as below in 3 rows for each database for each type of
backup.I believe I need to compare consecutive datetime column
rows and conclude as below for each database for each type of
backup to comeup with the belwo format.
MyServerAMyDBWeekly Full Backup on Saturdays at 1:00 AM D:\ABC.bakFULL
MyServerAMyDBDaily Diff Backup at 1:00 AM D:\ABC.diffDIFF
MyServerA MyDB Daily T-Log Backup every 4 hours D:\ABC.log T-Log
And also I have to do this for all databases on the server to
find out full/diff/tlog job schedules
Any suggestions.
I figured I can take sample data for any database from the
last 30days and then I can come up with something like this.
MyServerAMyDBWeekly Full Backup on Saturdays at 1:00 AM D:\ABC.bakFULL
MyServerAMyDBDaily Diff Backup at 1:00 AM D:\ABC.diffDIFF
MyServerA MyDB Daily T-Log Backup every 4 hours D:\ABC.log T-Log
Any suggestions please.
There's a lot here. I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.
As suggested I looked up the backupset tables and I think we can still
come up with schedules and timings for all databases and their respective
kind of backups if we can use most recent sample data (last 30 days)
The backupset table is not a schedule. A schedule implies what is supposed to occur, backupset lists what actually occurred. I've attached a script that I give to the junior DBA's that lists the backups by type for each database.
And where can I find the information regarding which tool is doing backups
- sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
which says even if a 3rd party does backups sometimes the msdb tables
can still carry sqlserver to be the one doing backups.
Sorry. This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it. There is likely an agent or service that is running on your servers that you can look for. A typical third party setup would be a centralized backup management server, and agents on each individual server. I suggest you track that down and find it. It greatly concerns me that you are guessing about something as critical as backups.
And also I have to do this for all databases on the server to
find out full/diff/tlog job schedules
Any suggestions
Like a said above, the tool being used to take backups will give you the schedule. A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 29, 2020 at 1:11 pm
All you can do with the information you have is infer the schedule and plan. That's it. You can't know what the intent is. You can't know what the Service Level Agreement is. You can't know what the Recovery Point Objective or Recovery Time Objective is. To know all that, you have to work with the business and with the people who set up the schedules, wherever they are, using whatever tool.
What you have is not the intent or the plan. You have the results. That's all you'll ever have. You can infer the plan from those results, but it's just an inference. If you want the plan, you should go where it is.
If I can ask, what are you trying to solve here? What's the concern? I'm a little unclear where the end game is. Will you somehow start making changes to backups based on this info? Are you planning to take this to the business to get an SLA and set the RPO & RTO? I'm just a little confused what you think you'll have at the end of this operation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2020 at 2:22 pm
Ah... yeah. I get what you're trying to do. You're trying to look at "actuals" by database to ensure that the backups are not only following a backup plan but you're also trying to determine what the plan is by database so that you can check on whether it's sufficient or not. It's also great for proving things to auditors, as well.
I like the idea a lot and so let's turn this into a two way street where we'll both benefit. You post your actual code that you use to come up with the summary and I'll have a go at making the output you've asked for. I'll use your code to create the "test data" that I need to pull this off using my own servers as the source of that data.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2020 at 3:47 pm
Close enough and yes .(excepting the auditors part !) I will post code once I make progress.
October 29, 2020 at 3:50 pm
Yes, I am trying to draw the inference from the results. If necessary I may need to make changes but not yet there.
October 29, 2020 at 3:53 pm
Apologies, I was not clear entirely in my first post.
I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.
I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.
October 29, 2020 at 4:12 pm
Apologies, I was not clear entirely in my first post.
I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.
I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.
Does the script I attached not get you almost everything you need?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 29, 2020 at 5:30 pm
Close enough and yes .(excepting the auditors part !) I will post code once I make progress.
I'm not quite sure what you mean by that. Your original post says you already have a script that creates the output you cited in your first post. That's all I'm after.
As a bit of a sidebar, all of your posts have seemed quite evasive when people ask for additional information they need to help you. You're actually driving people away from helping you... like you just did me. 🙁
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2020 at 5:31 pm
mtz676 wrote:Apologies, I was not clear entirely in my first post.
I got the table result from msdb.dbo.backupset, master.sys.sysdatabases and msdb.dbo.backupmediafamily.
I am trying to draw inference from the results and see the backup schedules and then see if we need to make any changes. But first I need to know what is going on.
Does the script I attached not get you almost everything you need?
From the wording in your reply, I didn't even look for an attachment. 😉 Perhaps the op ran into the same issue.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2020 at 6:16 pm
Yes I do. I thought you meant after I make progress on that script. Anyways here it is.
SELECT
@@servername [ServerName]
,master.sys.sysdatabases.name [DatabaseName]
,msdb.dbo.backupset.backup_start_date [BACKUP START]
,msdb.dbo.backupmediafamily.physical_device_name[FILE Location]
,CASE
WHEN msdb.dbo.backupset.type = 'D'
THEN 'FULL'
WHEN msdb.dbo.backupset.type = 'I'
THEN 'Diff'
WHEN msdb.dbo.backupset.type = 'L'
THEN 'Logs'
END [BACKUP TYPE]
FROM
msdb.dbo.backupmediafamily,
master.sys.sysdatabases
LEFT OUTER JOIN
msdb.dbo.backupset
ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and master.sys.sysdatabases.name = 'MyDB'
and msdb.dbo.backupset.type = 'D'
and CONVERT(date, backup_start_date) > getdate()-30
ORDER BY [BACKUP START] DESC;
October 29, 2020 at 6:44 pm
Does this get you closer?
SELECT @@servername [ServerName],
SDB.name [DatabaseName],
BS.backup_start_date [BACKUP START],
BMF.physical_device_name [FILE Location],
CASE
WHEN BS.type = 'D'
THEN 'FULL'
WHEN BS.type = 'I'
THEN 'Diff'
WHEN BS.type = 'L'
THEN 'Logs'
END [BACKUP TYPE]
FROM master.sys.sysdatabases SDB
LEFT OUTER JOIN msdb.dbo.backupset BS ON SDB.name = BS.database_name
LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE SDB.name = 'YourDB'
AND backup_start_date >= DATEADD(day, -30, GETDATE())
ORDER BY BS.backup_start_date DESC;
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 29, 2020 at 10:33 pm
There's a lot here. I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.
As suggested I looked up the backupset tables and I think we can still
come up with schedules and timings for all databases and their respective
kind of backups if we can use most recent sample data (last 30 days)
The backupset table is not a schedule. A schedule implies what is supposed to occur, backupset lists what actually occurred. I've attached a script that I give to the junior DBA's that lists the backups by type for each database.
And where can I find the information regarding which tool is doing backups
- sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
which says even if a 3rd party does backups sometimes the msdb tables
can still carry sqlserver to be the one doing backups.
Sorry. This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it. There is likely an agent or service that is running on your servers that you can look for. A typical third party setup would be a centralized backup management server, and agents on each individual server. I suggest you track that down and find it. It greatly concerns me that you are guessing about something as critical as backups.
And also I have to do this for all databases on the server to
find out full/diff/tlog job schedules
Any suggestions
Like a said above, the tool being used to take backups will give you the schedule. A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.
Michael,
The aim you were trying to achieve with 3 scans of backup_set's could be easily achieved with a single LEFT JOIN and CASE statements in the SELECT part of the query.
_____________
Code for TallyGenerator
October 29, 2020 at 10:53 pm
Michael L John wrote:There's a lot here. I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.
As suggested I looked up the backupset tables and I think we can still
come up with schedules and timings for all databases and their respective
kind of backups if we can use most recent sample data (last 30 days)
The backupset table is not a schedule. A schedule implies what is supposed to occur, backupset lists what actually occurred. I've attached a script that I give to the junior DBA's that lists the backups by type for each database.
And where can I find the information regarding which tool is doing backups
- sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
which says even if a 3rd party does backups sometimes the msdb tables
can still carry sqlserver to be the one doing backups.
Sorry. This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it. There is likely an agent or service that is running on your servers that you can look for. A typical third party setup would be a centralized backup management server, and agents on each individual server. I suggest you track that down and find it. It greatly concerns me that you are guessing about something as critical as backups.
And also I have to do this for all databases on the server to
find out full/diff/tlog job schedules
Any suggestions
Like a said above, the tool being used to take backups will give you the schedule. A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.
Michael,
The aim you were trying to achieve with 3 scans of backup_set's could be easily achieved with a single LEFT JOIN and CASE statements in the SELECT part of the query.
Well, it’s an old script that I rarely use. It was part of a “here’s stuff, can you figure this out” training that I stuck together a while ago for some junior DBA’s.
The exercise was to take that code, figure it out, add backup sizes and elapsed time to it, and automate it to send a morning report.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 30, 2020 at 1:13 am
There's a lot here. I'm not sure I am 100% clear about what you are looking for. I will take stab at what I think I understand you are looking for.
As suggested I looked up the backupset tables and I think we can still
come up with schedules and timings for all databases and their respective
kind of backups if we can use most recent sample data (last 30 days)
The backupset table is not a schedule. A schedule implies what is supposed to occur, backupset lists what actually occurred. I've attached a script that I give to the junior DBA's that lists the backups by type for each database.
And where can I find the information regarding which tool is doing backups
- sql or 3rd party.Is it available in the msdb backupset tables.I read a blog
which says even if a 3rd party does backups sometimes the msdb tables
can still carry sqlserver to be the one doing backups.
Sorry. This may sound nasty, but if a third party application is doing backups, someone in your organization should know about it. There is likely an agent or service that is running on your servers that you can look for. A typical third party setup would be a centralized backup management server, and agents on each individual server. I suggest you track that down and find it. It greatly concerns me that you are guessing about something as critical as backups.
And also I have to do this for all databases on the server to
find out full/diff/tlog job schedules
Any suggestions
Like a said above, the tool being used to take backups will give you the schedule. A backup may be scheduled to run at midnight, but the actual backup of a database may not occur until much later.
Wow. That's some old and still working code, Michael. It still has a call to dbo.sysdatabases in it that still works. That's a great audit report, especially since it's so very simple.
Unfortunately for me, I get a shedload of "Logfile is Missing" failures. It's not the fault of the code. It's my "fault" and it's not a fault... it's a feature. It's like when I do the laundry... I only wash the dirty clothes and not the clean ones. I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and I skip that backup.
If you combine the latter tidbit of knowledge with your report, it turns out that I have several databases that haven't suffered any modifications for well over 30 days and I'm going to have to get after some people. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply