Need a backup script to get backup history status for the latest backup from all linked sql servers
-
Hi Guys,
I am actually in need of a t-sql or a powershell script that will help me get the latest backup of all sql servers from a central server. We already have our environment setup for this i.e one central server and there are quite a handful of linked servers. I would like to have a script which will scan all of these linked servers get the info back, and send me a report of the latest backup details either in an "HTML" format or possibly using some sort of tabular formatting so that it looks good. I know that many of them might have implemented this in their environment, but this is the need of the hour for me. You can get back to me either providing a comment below or sending a private e-mail on this id faisalfarouqi@gmail.com.
I hope to see a quick response from you experts.
I am not a good programmer by any means as I have almost started off my career in "Sql Server", and would really appreciate your help.
Regards,
Faisal
-
PowerShell would be great for this. Using the SQL Server Provider you can iterate over the Linked Servers of your Central Server and issue a query to each. Then you can export the results to a CSV file, or store them in a database.
This could also be done in T-SQL using the same algorithm and some dynamic SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
If you use T-SQL, you'll probably want to use a cursor to loop through all of the linked servers. You can find all the linked servers in the Sys.Servers system view. You'll probably want to filter by the provider column to limit it to links to other SQL Server machines.
Then for each link, you'll want to get the backup information you are interested in. You can find backup information in the following tables:
msdb.dbo.backupset
msdb.dbo.backupmediafamily
Master.sys.databases
I believe they join together like this:
<linked server name>.msdb.dbo.backupset Backup_Set
Join
<linked server name>.msdb.dbo.backupmediafamily Media
ON
Backup_Set.Media_set_id = Media.media_set_id
AND Backup_Set.is_copy_only = 0
Left Join
<linked server name>.Master.sys.databases DB
ON
Backup_Set.database_name = DB.name
The whole backup history is in there, so you'll have to determine which backups you want and what backup information you are looking for and filter appropriately.
Once you have all the pieces, you use your cursor to loop through the linked servers, use dynamic SQL to run your query for each linked server, collect all the results in a temp table or table variable, and then select those results back out formatted as HTML...
http://gallery.technet.microsoft.com/scriptcenter/Selection-result-as-HTML-a1ccff98
...and send them off in an email with the HTML results variable as the body.
I hope this helps. Good luck!
-
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).
Pls. Pls. Pls. help me out here....I badly need a script
Regards,
Faisal
-
faisalfarouqi (1/19/2013)
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).Pls. Pls. Pls. help me out here....I badly need a script
Regards,
Faisal
First, what would you learn if we gave it to you. Second, it's possible those of us reading this thread don't have something handing.
Here is what I suggest. Start by writing something that achieves your goal locally, and if you have problems, post back here what problem(S) you are encountering, the code you have developed so far, and I am sure some one would be more than willing to help you work through the problem. You will learn more that way then someone just giving you the code. Plus you will have a better understanding of the code as you are the one who will need to support the code if something goes south on you.
I know I wouldn't want to hear "I don't know how it works, I just got it off the internet." We are here to help, and that includes making sure you understand the code you will have to support.
-
Hi Lynn,
You are absoultely right here, that one gets a better understanding of things when they write the code themselves, and I really respect that. Currently, I am in a different situation out here, and since my knowledge of coding (I can't even rate that now, since I've just started off) is quite low at this point, and the time is ticking by as I have to finish off this task at least in a day or two, otherwise I'll be a swiss cheese.
I know it's sounds a little bad to not attempt to code a few things, and ask directly for a script, but I can't help it now as I am stuck, and really need one badly. I appreciate everybody's help and support out here.
Regards,
Faisal Farouqi
-
Well, that leaves me out as I have nothing at hand and I am not going to take the time to try and write something, especially since I don't have the environment to even test it.
Good luck.
-
Lynn Pettis (1/19/2013)
faisalfarouqi (1/19/2013)
Thank you guys, but frankly speaking I kind of need a general script which can be run by changing some parameters, and then it should loop through all linked servers, get back the result, and send an e-mail to the DBA's. As I said earlier it's kind of really difficult to code these in the initial phase of my career, so I would pls. request you if possible could you send PM/Mail me a generalized script which can do all this stuff for me. I don't want to use any 3rd party tool becoz of the licensing cost (my company can't afford it).Pls. Pls. Pls. help me out here....I badly need a script
Regards,
Faisal
First, what would you learn if we gave it to you. Second, it's possible those of us reading this thread don't have something handing.
Here is what I suggest. Start by writing something that achieves your goal locally, and if you have problems, post back here what problem(S) you are encountering, the code you have developed so far, and I am sure some one would be more than willing to help you work through the problem. You will learn more that way then someone just giving you the code. Plus you will have a better understanding of the code as you are the one who will need to support the code if something goes south on you.
I know I wouldn't want to hear "I don't know how it works, I just got it off the internet." We are here to help, and that includes making sure you understand the code you will have to support.
I agree with Lynn and I also understand your situation, but you should at least try to create something first and then ask for advice on your code and believe me you will be surprise with how fast you will be able to do by yourself. You already got some tips, try to write something based on that. When you have got it running posted here so that other people can also benefit from it. Remember why you are actually asking is for people to write something for you on their free time, when you have not even try to do it yourself.
-
Lynn and Ignacio are right. If you are responsible for these servers, it's important that you start developing the skills and knowledge you need to maintain them. Running someone else's code that you don't understand on your servers is very risky. This isn't too difficult a task, but it does involve a wide array of concepts. My post above outlines the overall process for doing this in T-SQL and hopefully gave you enough details to start doing some research and get started. If not, feel free to ask questions. You should really give it a shot yourself and post back with specific issues you encounter.
-
Thanks. a lot once again, and really do appreciate what you guys say. I'll try to figure out how best I can utilize the info provided by "sestell1". Pls. pardon my ignorance, but I would really like to know how do others get backup information from all of the instances configured in their for e.g: if there are more than 50+ servers, I hope nobody does that manually going and checking on each of the servers, they might be using some kind of a script right? or either a 3rd party tool to do the work for them.
However, I am surprised to know that no one actually has set this thing up int their environment.
I need to really code things off by myself, rather than placing a request for the same.
Regards,
Faisal
-
I imagine everyone has their own approach to this.
Your approach of using linked servers will definitely work.
I use a C# application I wrote that lets me run T-SQL code against a select list of servers and provides a combined result set.
I also have an Excel spreadsheet with a macro that essentially does the same, but puts results in individual worksheets. I don't manage anywhere near that number of servers though.
Someone else I know uses registered servers somehow to run T-SQL against multiple servers.
As opc.three mentioned, you could probably do this through a Powershell script as well.
I don't know, but I'd guess Redgate offers some sort of tool for this sort of thing.
-
faisalfarouqi (1/19/2013)
Thanks. a lot once again, and really do appreciate what you guys say. I'll try to figure out how best I can utilize the info provided by "sestell1". Pls. pardon my ignorance, but I would really like to know how do others get backup information from all of the instances configured in their for e.g: if there are more than 50+ servers, I hope nobody does that manually going and checking on each of the servers, they might be using some kind of a script right? or either a 3rd party tool to do the work for them.However, I am surprised to know that no one actually has set this thing up int their environment.
I need to really code things off by myself, rather than placing a request for the same.
Regards,
Faisal
Every company has a different approach to it. If you want to use something that has been already proven and you want a step by step guide, you may want to try http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61621/.
-
sestell1 (1/19/2013)
I imagine everyone has their own approach to this.Your approach of using linked servers will definitely work.
I use a C# application I wrote that lets me run T-SQL code against a select list of servers and provides a combined result set.
I also have an Excel spreadsheet with a macro that essentially does the same, but puts results in individual worksheets. I don't manage anywhere near that number of servers though.
Someone else I know uses registered servers somehow to run T-SQL against multiple servers.
As opc.three mentioned, you could probably do this through a Powershell script as well.
I don't know, but I'd guess Redgate offers some sort of tool for this sort of thing.
You can use CMS (central management server) to run scripts against multiple servers at once.
Besides the powershell option there is also ssis which is very good for this.
If you read my blog, you will also find a script to check the backup history.
http://jasonbrimhall.info/2012/11/30/backup-history/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
faisalfarouqi (1/19/2013)
Thanks. a lot once again, and really do appreciate what you guys say. I'll try to figure out how best I can utilize the info provided by "sestell1". Pls. pardon my ignorance, but I would really like to know how do others get backup information from all of the instances configured in their for e.g: if there are more than 50+ servers, I hope nobody does that manually going and checking on each of the servers, they might be using some kind of a script right? or either a 3rd party tool to do the work for them.However, I am surprised to know that no one actually has set this thing up int their environment.
I need to really code things off by myself, rather than placing a request for the same.
Regards,
Faisal
What we are trying to say is that we are willing to help you by providing you with guidance and feedback as you develop your process and write what ever code you decide to use (PowerShell, T-SQL, or something else).
None of us have said we haven't done this in our own environments, what we have said is that we aren't willing to just give you code to run in your environment. If we give you the code and something goes wrong, you are the one that needs to support the code, not any of us. We want you to fully understand what it is you are implementing in your environment.
As an aside, no, I haven't implemented anything like this in my environments. It was already setup in the environments (last two contract positions), or the environments were small enough that it wasn't necessary (only had about 5 or so production servers running SQL Server, so using SQL Server Agent was sufficient for our needs.
My current production environment happens to be 7700 miles away, and until I get there, I'm not sure what they do over there. I'm looking forward to learning.
-
Thanks. to all you guys.
I've finally come up with a script that does the trick for me, and can be helpful for others as well. Now, here are the problems which I am facing...currently I just want to have the differential backups to be specified only if there is one on the server in the report, and it shouldn't print out diff backups in the report if there isn't any. Secondly, I want the report to be nicely formatted, so it looks good to the eyes, anything in html would do for me.
I've attached the script with this post, and I hope now people can actually provide me with some inputs on how to achieve it in a more neat & tidy manner. You can run this in your environment, and can modify the code to suit my needs mentioned above. Pls. revert this to me, as I am in need to get this formatted, and avoid those differentials & log backups if they aren't really there.
--Pls. note I've also attached how the report looks when it arrives in your mail box.
I hope this helps, and now you guys can make me achieve this in a better way.
Regards,
Faisal
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply