Here's the basic script I use to backup all DBs in each SQL Server instance on a machine, except tempdb.

This is saved as a Powershell.ps1 script, and run via an SQL Agent job every night, on each machine.

You can spruce it up with some error checking, validation, notifications, etc...

You can create another Powershell script which can copy this .ps1 to each target server and create the SQL Agent job and schedule that starts it. This makes it a simple matter to update the script as needed, and to add and schedule it on new servers.

Jeff makes 2 good points:

- centralizing the backup job on a single server = potential single point of failure.

- in many cases, 24 hours is a long time to go between backups. As always, "It depends".

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null

#clear

$MachineName = $Env:COMPUTERNAME

$Servers = get-childitem SQLSERVER:\SQL\$MachineName | select name

foreach ($Server in $Servers)

{

$Srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server.Name

$DBs = $Srv.Databases | where {$_.Name -ne "tempdb"}

$BackupFolder = $Srv.BackupDirectory

foreach ($DB in $DBs)

{

$RecoveryModel = [string]$Db.RecoveryModel

$DBName = $DB.Name

$FileNameData = $BackupFolder + "\" + $dbname + "_Dump.bak"

$FileNameLog = $BackupFolder + "\" + $dbname + "_Log_Dump.bak"

$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Action = "Database"

$dbBackup.Database = $dbname

$dbBackup.Initialize = $true

$dbBackup.Devices.AddDevice($FileNameData, "File")

$dbBackup.SqlBackup($srv)

if ($RecoveryModel -ne "Simple")

{

$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Action = "Log"

$dbBackup.Database = $dbname

$dbBackup.Initialize = $true

$dbBackup.Devices.AddDevice($FileNameLog, "File")

$dbBackup.SqlBackup($srv)

}

}

}