2

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.

2

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.

0

I am using the following, this is only for a few servers but maybe you can adapt for your case :

1. Create an extra DB on your instance or use master to host your stores procedures, for since code to backup your DB (SP dbo.backup_db).

2. Create PowerShell script backup_db.ps1 with cmdlets "invoke-sqlcmd" to call your store procedure :

$invoke=@"
USE YOURDB
GO
exec [dbo].[backup_db];
"@

Invoke-Sqlcmd -Query $invoke -ServerInstance MACHINENAME\INSTANCE

Before this you will have to check your execution policy is "RemoteSigned" and import the sql module

Get-ExecutionPolicy
import-module sqlps

3. Add, Program and Start your PowerShell script with the Windows tasks scheduler :

Action : Start a program. program/script: put the full powershell path :

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe

Add arguments : put the script and his full path, do not use quotes.

Start in(optional) : directory where your script reside, without quotes

Works for me with success for months !

Not the answer you're looking for? Browse other questions tagged or ask your own question.