Following points to be accomplished -
- Get all the databases restoration information
- From all reporting, staging or testing environments
- Information about when the database restoration started and completed
- If the restoration is a full restore or differential
- When the backup file was ready
- Above said information is to be received through Email in a tabular format.
Well, it seems to be easy, and of course it's easy once you know it.
Below are the implementation steps.
- Write a SQL code to retrieve the complete information about backups. This includes when the backup file was ready, the restoration type, restoration start date time and end date time. Data restoration completed date time is unavailable in backup tables hence so get the information from the logs.
- Create a Power-Shell script to automate this process and fetch the data restoration information from across various reporting/staging/testing servers.
- Store this information in a table to maintain a history. This will help in analyzing if the restoration time is increasing/decreasing over a period of time.
- Fetch this information from the table and convert it into HTML tabular format. Send the information through Email using "db Mail".
Certainly, there will be some other work-around, or smarter methods version to achieve this goal. But since this solution solves my purpose and is working perfectly, I didn't dig deeper to find a shorter or better implementation.
Here is my table which will hold the data restoration information.
Upon execution you will get the complete data restore information from the reporting server in which you had executed the code. However the requirement is to fetch the same information from across various servers. Hence write a Power-shell script as mentioned below.
clear;
$Servers = Get-Content "D:\Monitoring_RestoreInfo\Computers.txt";
foreach($computer in $Servers)
{ invoke-sqlcmd -inputfile "D:\Monitoring_RestoreInfo\Restore Information SQL Server.sql" -serverinstance $computer -database "msdb" -ErrorAction 'Stop' -Verbose -QueryTimeout 1800 # the parameter -database can be omitted based on what your sql script does.
}
Note: Computers.txt has all the names of the reporting/staging/testing servers.
Create a windows scheduled task in the server in where you have an access to other servers. The screenshots are given below.
Arguments provided are as follows:
- Program/Script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
- Add arguments (Optional): D:\Monitoring_RestoreInfo\DBRestoreInfo.ps1
- Start In : D:\Monitoring_RestoreInfo\
Now let's go back to SQL Server database in which you have created the table. Write a script to convert the data into HTML format.
Create an SQL Agent Job to automate this process and run it every morning after execution of the Power-shell script.
Thank you for visiting my blog. Please do let me know if you need more clarification in this implementation.
