Running multiple servers script

Question

Running multiple servers script

Lou

SSCrazy

Points: 2929
More actions
August 26, 2016 at 8:06 pm

#329687

Hi community,
Sorry to publish this question on friday :cool::angry:
Here is the question:
I'm running a server farm with 25 inherited sql server (EE) instances (SQL Server 2008, SQL Server 2008 R2)
the site management is not very sure about how long or if finally all their backups are working fine, some are very lenghty in time.
What could be my best option, to run a script to just check what databases are backed up day after day, also consider that they are running a third party "data protection suite" that connects to each instance a do their job. I have done my script, it runs fine in just one instance, but when I see other instances, I could see that the task is not fully completed. Would I need to copy my script in each server and run it ???.
How could I run an unattended task to check all my servers, this servers are not linked, or perhaps I am demanding too much for SQL Server ?
I'm only checking
Perhaps some of you have a clear point of view, sorry for the question I am newbie with SQL Server 2k8
Thanks for your ideas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 1

Depends on what you are doing. If you just want to know status of all the backup jobs across all servers you could just use built in Registered servers task. I blogged on this here . If you are deploying scripts across many different databases, try Red Gate's multiscript tool. Both are amazing tools, just depends what you want to do.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

If you have your servers registered in SSMS, you can right-click on a group and click "New Query" in the context menu. The query window that opens will be connected to all the servers in that group. You can then run your query of the backup history and it'll return the results from all the different servers. It includes the name of the server as an additional column in the result set.

Beyond that, you're looking at a product like mentioned above.

As for an unattended execution, you could create a job on each server to check the backups and send you an email if there are any that haven't been backed up.

I think there's a larger question you need to ask. If the company is using a third party "data protection suite" to do the backups, doesn't it have some type of reporting you could use to tell what it did? If you don't trust it, should you be using it for your backups? I use native SQL backups myself. I've heard good things about Redgate SQL Backup Pro, but I've never used it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

@luismarinaray,

Both answers that you've marked as "Worked for the OP" have multiple suggestions. Which method did you ultimately end up using?

--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)

Lou SSCrazy Points: 2929 More actions · Answer 4

Definitely,

I use, the execute SQL Queries across multiple servers, both solutions point to that direction.

And is the more simple solution, that I ever could image and use, KISS principle is important.

I was thinking in powershell, but it deserve some time and investigation.

Thanks for your ideas