September 27, 2012 at 5:50 am
Have got a client requirement where we need to find the logbackup intervals (Occurrence of the log backups (i.e. every 15, 30, 60 etc)) in all the databases across all the servers.
Could anyone please help me with a script which can fetch the above detail? We have all the editions of SQL server under the support.
Thanks in advance.
September 27, 2012 at 5:59 am
Check the schedule of the job which is running the task.
Otherwise query msdb.dbo.backupset which will help contain the information you need.
September 27, 2012 at 6:04 am
Thanks for the reply Antony. But that will be a tedious task to go to the job and check the schedules for all the databases across many servers. Hence looking for a query which will ease the work.
September 27, 2012 at 6:06 am
Is the name of the job the same or the name of the schedule on each server? If so again query the job or schedule tables to get the information, otherwise it will be a look in backupset taking the difference of two backup start times.
September 27, 2012 at 6:13 am
Yes Antony. The name of the job remains same across all the servers
September 27, 2012 at 6:15 am
well, if thats the case then query msdb.dbo.sysjobs, link it to sysjobschedules then link that to sysschedules for the job id.
September 27, 2012 at 6:23 am
Thank you. Seems like I am heading in the correct direction with your help. I have now queried msdb.dbo.sysjobs. But how to link it with sysjobschedules then link that to sysschedules for the job id :(.. I am relatively a newbie to SQL server
September 27, 2012 at 6:24 am
If it's the number of servers you need to run this on that's the problem, then you have a couple of non pure SQL options as well (assuming they aren't all running as linked servers on one instance?).
You'll need a SQL query as described above, but then to run it against all the servers you'll need to:
Either register all of them in SSMS and use that functionality to run a query against all of them. (more info here - http://www.sqlmag.com/article/sql-server/registered-servers-142469)
Or run to your scripting language of choice (probably powershell) and knock something up that will run the query against a list of servers (http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/ - for an overview of this option)
Training cats makes SQL Server look easy
September 27, 2012 at 6:28 am
Thanks Grasshopper. I am planning to register all the servers in SSMS but looking out for a query which does the same
September 27, 2012 at 6:31 am
Query the tables individually, then you should notice the common columns between the three tables
Job_ID & Schedule_ID appear in 4 times, in different tables, this is the linking route between the three tables
sysjobs.job_id to sysjobschedules.job_id
sysjobschedules.schedule_id to sysscheduled.schedule_id
September 27, 2012 at 7:03 am
ranganathleo (9/27/2012)
Thanks Grasshopper. I am planning to register all the servers in SSMS but looking out for a query which does the same
Unforunately there's no way to query a remote sql server without adding it as a linked server. You can add them on the fly with:
set @oserver='server1\instance'
EXEC master.dbo.sp_addlinkedserver @oserver, @srvproduct=@oserver, @provider=N'SQLNCLI', @datasrc=@oserver
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@oserver,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
If you mean you want to register them with SSMS through a query, then you'll have to write something that can output the XML format that SSMS uses for export/import of reg servers.
Training cats makes SQL Server look easy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply