We’ll start with the simplest form of backup script, taking a full backup of a database. This demonstrate’s the basic principles that will then be expanded on to perform more complex operations later on in the series.

The script is:

Import-Module "SQLPS" -DisableNameChecking

$ServerName = "Server1\SQL2012"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database
$Db = $SQLSvr.Databases.Item("psdb1")

$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$backup.BackupSetDescription = "Full Backup of "+$Db.Name
$Backup.Database = $db.Name

$BackupName = "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)

$Backup.Devices.Add($BackupDevice)
$Backup.SqlBackup($SQLSvr)
$Backup.Devices.Remove($BackupDevice)

Now to break it down:

Import-Module "SQLPS" -DisableNameChecking

$ServerName = "Server1\SQL2012"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

$DbName = "psdb1"
$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database
$Db = $SQLSvr.Databases.Item($DbName)

First we import the SQL PowerShell module, we use the -DisableNameChecking to hide this error message:

WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less discoverable. To find the commands with unapproved
verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.

which is just letting us know that Microsoft don’t always follow Microsoft’s recommended best practice

Next we build our connection to our SQL Server by putting the Server and Instance name into a string variable and then passing it into the constructor for a new SQL Server object. This examples assumes that you are using Windows Authentication to connect to your server and you are running the script under an account that has permissions to the server. You can pass the value straight to the constructor, but I find having well name variables towards the top of the script much easier to find and you can reuse them throughout, so you don’t have to search and replace each time you want to work on a different server,

Then we create a new Database object by passing the name of the database we want to backup to the constructor

The next section:

$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$backup.BackupSetDescription = "Full Backup of "+$Db.Name
$Backup.Database = $Db.Name

This creates a new Backup object, and then we say what type of backup it is. In this case, we used the Database type. For later ease we name the backup. Then attach the database to be backup up.

$BackupName = "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
$DeviceType = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$BackupDevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($BackupName,$DeviceType)

Now we create our backup device. $BackupName for the File backup device is the path to the .bak file. Remeber that the path is relative to the SQL Server Instance you’re asking to perform the backup. ie; that c:\psbackups folder is on Server1, not the machine you’re running the PowerShell on. The type of device and path are passed into the constructor for a new backup device.

And now finally, we come to do the actual backup:

$Backup.Devices.Add($BackupDevice)
$Backup.SqlBackup($SQLSvr)
$Backup.Devices.Remove($BackupDevice)

Add the newly created backup device to the backup object. Perform the actual backup, by calling the SqlBackup passing it the SQL server object we created before. The referenced SQL Server instance will now perform the backup before handing back to the script, so if you’ve pointed this at a 50GB database and are backing it up to slow disks this might be a good time to grab a coffee.

Once the backup has completed the script continues, and we remove the backup device from the backup object

In this case the same operation using Backup-SQL requires much less script:

Import-Module SQLPS -DisableNameChecking
$ServerName = "Server1\SQL2012"
$DbName = "psdb1"
$BackupFile ="c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
Backup-SQLDatabase -ServerInstance $ServerName -Database $DbName -BackupFile $BackupFile -BackupAction Database

As you can see, this new cmdlet removes the need for the scriptwriter to manually build the SMO objects manually as here it’s just taking in strings, and doing the work on your behalf.

Tomorrow, we’ll be looking at looping this script across all the databases in a SQL Server Instance excluding those we don’t want.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.