how to perform regular backups of SQL Express 2008 R2 databases Which instruments to choose to implement backup strategy on 150 servers with SQL Servers Express Edition 2008 R2? The strategy should also include checkdb, index and statistics maintenance. In case of errors there should be some kind of notifications. Each of the machines is turned off every night.

Allowed data loss - 15 minutes.
I've found several ways to get the job done:


script + Windows Scheduler on every machine;
script + linked servers;
third-party software;
some other option


As my company is very intolerant to anything that implies extra bills I had 3 options left. Some kind of script plus Windows Scheduler seemed the most natural solution so I opted for it.

For scripting I chose Powershell as it is able to get every piece of the task done including sending e-mail notifications. I implemented this strategy.

This is where problems began. In fact, the main source of evil was the Scheduler On one machine some files were missing so the Scheduler did not work, on another machine  problems with permissions, on third machine the OS is too old and so on... So 10-20 machines out of 150 were not being backed up.

Running task every 15 minutes via Linked Servers was not a very good idea to me. At this moment I was solving other issue and realized that SQL Server   sp_procoption might be of help in my situation. A procedure that starts at SQL Server startup is a really good candidate for doing statistics and index maintenance, checking for corruption and backing up every morning and every 15 minutes subsequently.

I used Ola Hallengren's maintenance script to reindex, update stats and checkdb. Of course modified a little to meet my company's needs. It also writes data on backups and checks to a specific table which can be queried via Central Management Server for analysis.

Now the amount of DB's not backed up is no more than 2-3. And the only reason is that machines were not turned off.