Use Powershell: Backing Up Databases

Quick and easy script that will backup all databases (excluding system) in a specified instance to a folder. I'll go ahead and leave the download link up here for those who want to skip the lengthy explanations: Script Download Link...

Key takeaways -

How the connection to the SQL server is created.

1.Load SQL SMO assemblies.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

2.Create connection to SQL server.

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance

3.Get name of databases in SQL instance.

$databases = New-Object Microsoft.SqlServer.Management.Smo.Database $databases = $server.Databases

Test as you go: At this point, executing "write-host $databases" should give you something like this: [master] [model] [msdb] [tempdb] [testdb1] [testdb2] etc...

Backing up all non-system databases.

1.Differentiating between system/non system databases.
» Executing write-host $databases.issystemobject should give us an output such as: True True True True False
» Now we know that we can differentiate between system databases and nonsystem databases.
2.Run "something" for each non system database.

foreach ($Database in $Databases | where {$_.IsSystemObject -eq $False}){ something..... }

3.Setting up our backup object.(Note there are some variables in there that haven't been mentioned. Their use can be referenced in the full code below).

$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") $bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $bk.BackupSetName = $Database.Name + "_backup_" + $tStamp $bk.Database = $Database.Name $bk.CompressionOption = 1 $bk.MediaDescription = "Disk" $bk.Devices.AddDevice($BackupFolder + "\" + $Database.Name + "_" + $timestamp + ".bak", "File")

4.Running the backup.
Take note to the TRY/CATCH and the exception message on the CATCH. This is an error handler incase the database fails to backup. On failure, the exception message will be displayed in the console output.

TRY {     $bk.SqlBackup($server)     Write-Host "Backup Completed Succesfully: " $Database.Name -ForegroundColor Green} CATCH {     write-host $Database.Name " backup failed." -ForegroundColor Red     write-host "Error: " + $_.Exception.Message -ForegroundColor Red }

The best way to learn, download the script and play!

# Database Backup Script # by Zachary Higgins (9.29.2014) # Load SQL Assemblies [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null # Setup Console and Request User Information cls Write-Host Database Backup Script -ForegroundColor Cyan Write-Host # Prompt for manual entry of SQL Instance $SQLInstance = (Read-host -prompt "Enter SQL Instance Name [server\instance]") Write-Host "SQL Instance :" $SQLInstance -ForegroundColor Yellow # Prompt for backup folder Write-Host "Select backup folder..." $frmBackup = new-object -com Shell.Application $Backup = ($frmBackup.BrowseForFolder(0, "Select Folder", 0, "C:\")).self.path Write-Host "Backup Folder:" $backup -ForegroundColor Yellow Write-Host Write-Host # Console Output "Starting Backups" Write-Host "Starting Backups--------------------" -ForegroundColor Cyan Write-Host # Build SQL Connection $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance $databases = New-Object Microsoft.SqlServer.Management.Smo.Database $databases = $server.Databases $timestamp = Get-Date -UFormat "%y_%m_%d_%H_%M" foreach ($Database in $Databases | where {$_.IsSystemObject -eq $False}){ write-host "Database: "$Database.name $bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") $bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $bk.BackupSetName = $Database.Name + "_backup_" + $tStamp $bk.Database = $Database.Name $bk.CompressionOption = 1 $bk.MediaDescription = "Disk" $bk.Devices.AddDevice($BackupFolder + "\" + $Database.Name + "_" + $timestamp + ".bak", "File") TRY {$bk.SqlBackup($server) Write-Host "Backup Completed Succesfully: " $Database.Name -ForegroundColor Green} CATCH {write-host $Database.Name " backup failed." -ForegroundColor Red write-host "Error: " + $_.Exception.Message -ForegroundColor Red} write-host } Write-Host Write-Host "------------------------------------" -ForegroundColor Cyan Write-Host Write-Host Write-Host Write-Host "Finished!"

Script Download Link...

---