Determine Backup schedules across multiple SQL Servers

Today some architecture changes were scheduled to occur in the evening.  I wanted to adjust or disable backup jobs that could be affected.  I have some 50 SQL servers.  I needed to know when the backups were to start on each server.  I found a nice script at http://www.mssqltips.com/tip.asp?tip=1622to do that for a single server.  For multiple servers I  organized my SSMS “Registered Servers” as seen in the picture.  My 50+ servers are organized by SQL Version and by production status.  I can now right-click on “2000 ALL” (hilited blue), choose “New Query” and a query window opens up.  Look closely at the bottom of your query window and you’ll see something like: “Connected 11/11”.  This would say that your window is ready to query 11 of the 11 servers listed in the folder that you right-clicked on.  Next, I ran the query I got from MsSQLtips and voila!… i have the backup schedule for all my 2000 SQL servers!

Directory of Database Folders
Organize your servers for ease of queries

Leave a Reply

Your email address will not be published. Required fields are marked *