February 1, 2017 at 10:35 am
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
February 1, 2017 at 10:50 am
Paresh Motiwala - Wednesday, February 1, 2017 10:35 AMfolks
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.
Please do a search for the usage of tables in the MSDB database. It's all there.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2017 at 10:55 am
Jeff Moden - Wednesday, February 1, 2017 10:50 AMParesh Motiwala - Wednesday, February 1, 2017 10:35 AMfolks
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.Please do a search for the usage of tables in the MSDB database. It's all there.
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
February 1, 2017 at 12:08 pm
Paresh Motiwala - Wednesday, February 1, 2017 10:55 AMJeff Moden - Wednesday, February 1, 2017 10:50 AMParesh Motiwala - Wednesday, February 1, 2017 10:35 AMfolks
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.Please do a search for the usage of tables in the MSDB database. It's all there.
Hi Jeff
Thanks, I could not get any obvious answers, so if you have some feel free to share.
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
February 1, 2017 at 12:24 pm
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
February 1, 2017 at 12:31 pm
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
February 1, 2017 at 12:41 pm
Paresh Motiwala - Wednesday, February 1, 2017 12:24 PMso 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)
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
February 1, 2017 at 1:26 pm
Paresh Motiwala - Wednesday, February 1, 2017 10:55 AMJeff Moden - Wednesday, February 1, 2017 10:50 AMParesh Motiwala - Wednesday, February 1, 2017 10:35 AMfolks
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.Please do a search for the usage of tables in the MSDB database. It's all there.
Hi Jeff
Thanks, I could not get any obvious answers, so if you have some feel free to share.
"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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply