Linking a Database to its Backup Schedule on a SQL Server
-
folks
I am wondering if we can find, using a sql script, when a database was backedup and by using what schedule.
I not able to find the link between a database and its backup schedule.Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
- Paresh Motiwala - Wednesday, February 1, 2017 10:35 AM
Please do a search for the usage of tables in the MSDB database. It's all there.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, February 1, 2017 10:50 AM
Hi Jeff
Thanks, I could not get any obvious answers, so if you have some feel free to share.Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
- Paresh Motiwala - Wednesday, February 1, 2017 10:55 AM
In msdb, there are tables that start with dbo.backup
and there are tables that start with dbo.sysjob
Those are the tables that have backup and job information and those are the tables you would want to search on how to use for information you are after. I don't believe there is a table that you can query that has the backup, the job and the schedule if that's what you were looking for.
Jobs can have none, one or more schedules but backups themselves do not. Backups can be in none, one or many jobs. You can start searching on things like sql script to list backups, sql script to list jobs and schedules. I just did a search and got many hits for both.
It very well could be that the backup you are looking for wasn't executed through a job so it's probably a good idea to get familiar with those tables and how to use them.Sue
-
so yes, I found that in the sysjobsteps, there is a "command" column that lists the databases that are being backed up
from this, I can get a job_id which can be linked to a schedule.(I am gonna try that)Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
-
a good place to start with is probably sysjobhistory table in MSDB:
https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/You should be able to query this table, joined to sysjobs to find the job name and such this history is related to, and I believe the message_id of a backup is 3014, so something like this may be a place to start:
SELECT j.name, jh.step_id, jh.step_name, jh.sql_message_id, jh.sql_severity, jh.message,
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS run_datetime, run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 AS run_duration
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= CONVERT(int, CONVERT(varchar(8),GETDATE()-1,112))
--AND jh.message like '%BACKUP DATABASE%'
AND jh.sql_message_id = 3014
AND jh.step_id > 0
ORDER BY jh.run_date DESC, jh.run_time DESC - Paresh Motiwala - Wednesday, February 1, 2017 12:24 PM
Yes...exactly!! That could be one way to get what you are after. Remember how you are getting to that command column. Knowing about that one is really going to help you in the future, it comes in handy to search on that one to search for different things.Sysjobs and sysjobsteps are just joined on job_id. You can get a lot of good info from querying those.
Sue
- Paresh Motiwala - Wednesday, February 1, 2017 10:55 AM
"Obvious" answers aren't necessarily the best. I was hoping you'd spend a little time looking up "MSDB" in "Books Online", the free "help" system that you can download or use online ()MSDN, TechNet, etc) so that you'd know where to look for future answers on that important little DB.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply