Okay, so it is the second week of January 2015 and by now many resolutions that were made have been broken. Robert Pearl (b|t) challenges us to make and keep a T-SQL resolution of maintaining a healthy SQL environment, in this month’s T-SQL Tuesday blog party. When I looked at this the first thing that popped into my mind was backups. Every DBA worth his/her salt knows that backups are the first step in a healthy recovery plan. All the recovery plans and service level agreements do you no good if there is nothing to recover, which is why I built a little PowerShell script that lets me know if backups have not occurred. If you have a couple of servers and only a few databases on these servers then this script will save you very little time as you can manually check each server and each database. However, if you are dealing with many more servers and hundreds of databases, an automated check is just what you need.
I wrote this script to simplify my life as a DBA and to automate a very tedious, but important task. You can download this script along with my full presentation on how to use the script from here. As you go through the material you’ll find it is very simple to provide a list of servers, an email server if you want the report delivered to your inbox and be on your way to ensuring a healthy backup scheme. If you have any questions please fee free to drop me an email and I will gladly help you to implement my solution.
If backups are the first step in a healthy recovery plan, the final step is ensuring that you can restore those backups. I’m sure that you have heard of database professionals that routinely backed up their databases, but when it came time to recover from some catastrophic event the backups were no good. Don’t be that DBA. Make sure you test your backups to ensure you can restore them. I plan on building a PowerShell script to automate the testing of the restore process and will even include an Azure option, thus providing a complete and healthy recovery plan.