July 18, 2016 at 7:52 am
I have 40 SQL servers, (mostly standard SQL servers 2008,2012) and SQL express (Free).
Each of this 40 SQL server has multiple databases and each databases are backed up every night on SAN drive.
What i wanted is to create a query/report/process which automatically runs every morning to check all the backup ran okay and successful and email me the spreadsheet from all servers/databases saying what time/date last database was successful.
I've seen many people have setup that away not sure how they did that and what query they are using.
I know in some cases people have created task scheduler on their desktop which runs the queries and and send the excel sheet to their email id to give that information.
Please let me know ASAP if any one can guide me how to do that.
thanks
July 18, 2016 at 8:05 am
The query used depends on your own requirements.
For a quick and dirty view of the last backup date/time of each database, you could use something like this:
SELECT database_name ,
type ,
MAX(backup_start_date)
FROM msdb.dbo.backupset
GROUP BY database_name ,
type
ORDER BY type ,
database_name;
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 18, 2016 at 8:37 am
Guys, Just to clarify I know the query how to check up the last backup of the database from the server.
What I am looking for is the check that data by running script from my server.
It means what i want is to run the script to check 40 sql server and check 75 databases on those 40 sql servers and make sure to collect the data when was the last backup taken for each databases and create a excel or pdf or whatever report and email me the report.
please let me know how it can be possible
July 18, 2016 at 8:38 am
to run the query you posted i have to connect to each server individually to check the backup, i want script which should remotely monitor or backup and create a report
July 18, 2016 at 8:46 am
I would recommend an SSIS package to run the same query against each server and write the results to a central location, where you can report on them.
John
July 18, 2016 at 9:03 am
rk1980factor (7/18/2016)
to run the query you posted i have to connect to each server individually to check the backup, i want script which should remotely monitor or backup and create a report
Or you could use SQL Server Management Studio's Registered Servers feature, and create a server group with your servers in it, and run the query against all the servers at once...
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 18, 2016 at 9:05 am
John Mitchell-245523 (7/18/2016)
I would recommend an SSIS package to run the same query against each server and write the results to a central location, where you can report on them.
This is probably a better bet.
One thing you could look at is the techniques in David Bird's series here on SSC on "SQL Overview"[/url] - that'll give you a quick grounding in creating an SSIS package that spins round servers getting information from them.
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 20, 2016 at 12:19 am
You can use power shell script which will run on daily basis and sent you email in case of failure . I have created a power shell script which checks around 50 SQL Server and sent a consolidated report via email . If you need i will send the script to you .
July 21, 2016 at 6:50 am
that will be great if you can send me that power-shell script, let me know if you want emailaddress. also the steps you take to automate that script. also trying to find out a way to register 40 servers with register server with one query
July 22, 2016 at 9:27 am
Hi ..
There could be many ways to generate backup reports and send that to distributor ...
1. Create a centralize Database suppose here it is DBA_Activitiy
2. Create one .txt file which can contain all the mail id where you want to send reports, you can keep other information in this file.. Suppose here it is ReportCofig.txt
3. Create a table which will contain all the server/instance details, Suppose here it is ServerDtl_tbl
4. Create one SSIS package which will read the ServerDtl_tbls .. loop thru to all the SQL Instances .. Run the query to gather backup information from MSDB database and store the data into the Centralize Database (DBA_Activity)... Suppose one table BackupDtl_tbl
5. Create one VBScript to generate the report and send mail to distribution... This VB script will read the .txt file ReportConfig.txt
6. Create one schedule SQL Agent job which will execute the VB script for generating report and send report.
7. In that SQL job you can add steps to data purge from the BackupDtl_Tble, Delete old backup reports etc.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply