I came across a great little script this morning that backed up all of the user databases on a server, in responce to that I said that I had a PoswerShell script that did the same... and here it is for those that are interested.
Direct link to Post: TSQL Script to Backup all user Databases with time Stamped backup file.
$BackupPath = "C:\SQLServer\Backups"
$ServerName = "Localhost"
$DatabaseServer = "localhost\Developer"
# little helper function to ping the remote server.
function Get-ServerOnline ([string] $server)
{
$serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Server Online: $serverStatus"
return $serverStatus
}
# Load the SQL Server SMO Libraries
$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
{
# sql server version is 2008 or later, also load these other assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
}
$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Connecting to Database Server $ServerName"
if (get-serveronline($ServerName))
{
$s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $DatabaseServer
$dbs = $s.Databases
foreach ($db in $dbs)
{
if($db.Name -ne “tempdb”) # We don’t want to backup the tempdb database
{
$DatabaseName = $db.Name
if (!(Test-Path -path $BackupPath\$DatabaseName\))
{
$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
}
$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
$dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
$dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
$dbBackup.Action = “Database”
$dbBackup.Database = $DatabaseName
$dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + “\” + $DatabaseName + “_Database_” + $dt + “.bak”, “File”)
$dbBackup.SqlBackup($s)
}
}
$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Database Backup Complete"
}
3 Comments
Ahmed said
This is really very useful script.
karthick said
Great it is working thanks
Peter said
I really love to see a non-T-SQL-based but Powershell-based solution! Great, will definitely work on this one. Thanks a lot!