Performing backups of a database with Powershell

This article will show some Powershell script to perform a backup of a database with Powershell. Generating backups of a database is not that hard with Powershell. A backup is done by using the Server Management Objects or SMO. This is an individual download that can be downloaded for SQL Server 2008 (R2) or SQL Server 2012. I have used SQL Server 2012 Express, and this script also runs with the Express version of SQL Server 2012. I had to download additional packages for SQL from here:

Microsoft SQL Server 2012 SP1 Feature Pack

Obtain the SharedManagementObjects MSI-file and the PowershellTools MSI file, I downloaded the x64 version, but if you have x86 version of SQL Server 2012, use that instead. In addition, there are equivalent packages for Microsoft SQL Server 2008 (R2), Google is your friend here.. Here is the script to perform a backup. I have created a function or cmdlet called Backup-Database. There is also a function called PreLoad-SmoAssemblies, which I have not used, but this can be used to PreLoad the entire SMO Library if you want an example of how to do this.

            
function PreLoad-SmoAssemblies(){            
 $smoAssemblies = "Microsoft.SqlServer.Management.Common",            
"Microsoft.SqlServer.Smo",            
"Microsoft.SqlServer.Dmf ",            
"Microsoft.SqlServer.Instapi ",            
"Microsoft.SqlServer.SqlWmiManagement ",            
"Microsoft.SqlServer.ConnectionInfo ",            
"Microsoft.SqlServer.SmoExtended ",            
"Microsoft.SqlServer.SqlTDiagM ",            
"Microsoft.SqlServer.SString ",            
"Microsoft.SqlServer.Management.RegisteredServers ",            
"Microsoft.SqlServer.Management.Sdk.Sfc ",            
"Microsoft.SqlServer.SqlEnum ",            
"Microsoft.SqlServer.RegSvrEnum ",            
"Microsoft.SqlServer.WmiEnum ",            
"Microsoft.SqlServer.ServiceBrokerEnum ",            
"Microsoft.SqlServer.ConnectionInfoExtended ",            
"Microsoft.SqlServer.Management.Collector ",            
"Microsoft.SqlServer.Management.CollectorEnum",            
"Microsoft.SqlServer.Management.Dac",            
"Microsoft.SqlServer.Management.DacEnum",            
"Microsoft.SqlServer.Management.Utility";            
             
 foreach ($assembly in $smoAssemblies){            
  [void][System.Reflection.Assembly]::LoadWithPartialName($assembly);               
 }            
            
}            
            
            
function Backup-Database($dbinstance, $dbname, $saveToLocation = "C:\backups\")            
{            
             
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null            
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null            
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null             
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null             
            
 $dbserver = New-Object Microsoft.SqlServer.Management.Smo.Server $dbinstance            
 $bkup = New-Object Microsoft.SqlServer.Management.Smo.Backup             
 $bkup.Database = $dbname            
            
 $date = Get-Date             
 $date = $date -replace "\.", "-"             
 $date = $date -replace ":", "-"             
 $date = $date -replace " ", "-"             
            
 $file = $saveToLocation + $dbname + "_" + $date + ".bak"            
            
 $bkup.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)             
 $bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database             
            
 $bkup.SqlBackup($dbinstance)            
            
 Write-Host "Backup of database $database performed at $(Get-Date) to file location $saveToLocation"            
            
 trap [Exception]             
 {            
  Write-Host $_.Exception.Message            
  break            
 }            
            
}            
            
$dbinstanceToUse = $env:COMPUTERNAME + "\SQLEXPRESS"            
$dbnameToUse = "TestDatabase"             
            
Backup-Database $dbinstanceToUse $dbnameToUse             

The function or cmdlet Backup-Database takes three parameters. The $dbinstance is the computer name - db instance name to use for the $dbserver inside the function. The $dbname is the name of the database, while $saveToLocation is the location where to save the .bak backup file. Each .bak file will get a name of $dbname concatenated with a date stamp with the .bak extension in the end of the file name. The file location can be specified, but will default to c:\backups. Obviously, you want to either specify this or make sure that the location c:\backup exists first. The script can be adjusted by using Test-Path to make sure that the folder exists first.

This shows how Powershell can be used for a wide variety of tasks, such as performing a backup of a SQL server database.

Share this article on LinkedIn.