Day 2 of 31 days of SQL Server backup and Restore using Powershell: Looping through Databases

Yesterday we looked at backing up a single SQL Server database with PowerShell. There is certainly more code involved than a good old T-SQL style backup. But thanks to the wonders of PowerShell we now have a very reusable piece of code.

As a good example, if you wanted to loop through every database in a SQL Server instance we can now take the central part of the script and loop through it as many times as we want. And if we want to ignore certain DBs then that’s simple as well:

Import-Module "SQLPS" -DisableNameChecking

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

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -ne "tempdb"}){
    $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
    $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
    $backup.BackupSetDescription = "Full Back 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)
}

By changing one line we suddenly make the script a lot more useful:

foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -neq "tempdb"}){
    code
}

We get our SQL Server to return an object containing all the database objects present on the server($SQLSvr.Databases. We use the Where-Object cmdlet to filter down to only the databases we want. In this case, we’ve asked for all Databases where their name is not equal (-ne to TempDB. You can modify this fitering to exclude anything you’d like based on any Database Property, for example:

  • {_.Name -notlike "*_test"} – returns all databases that don’t end in _test
  • {$_.IsSystemObject -eq $FALSE} – returns all non system databases

Then using Foreach we loop through all the DB objects in the Databases object executing our backup code.

This can be extended further to loop through a list of servers as well:

Import-Module "SQLPS" -DisableNameChecking

$ServerList = @("server1","server2","server3")

foreach ($ServerName in $ServerList){
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

    $Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

    foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -ne "tempdb"}){
        $Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
        $Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
        $backup.BackupSetDescription = "Full Back 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 look at getting the same outcome using the Backup-SQLDatabase cmdlet:

Import-Module "SQLPS" -DisableNameChecking

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

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -neq "tempdb"}){
    $BackupFile = "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
    Backup-SQLDatabase -InputObject $SQLSvr -Database $Db.name -BackupFile $BackupFile  -BackupAction Full -BackupSetDescription "Full Back of "+$Db.Name
}

Again the backup script is shorter, though in this case we still need to use the SMO methods to create a connection to the SQL Server instance so we can loop through it’s collection of databases. And as we have it open, we can also pass it to the Backup-SQLDatabase cmdlet, though note that we have to use the -InputObject parameter rather than the -ServerInstance we used previously

This can be extended to multiple servers in exactly the same way as the SMO version:

Import-Module "SQLPS" -DisableNameChecking

$ServerList = @("server1","server2","server3")

foreach ($ServerName in $ServerList){
    $SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)
    $Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database

    foreach ($db in $SQLSvr.Databases | Where-Object {$_.Name -neq "tempdb"}){
        $BackupFile = "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"
        Backup-SQLDatabase -InputObject $SQLSvr -Database $Db.name -BackupFile $BackupFile  -BackupAction Full -BackupSetDescription "Full Back of "+$Db.Name
    }
}

Now we’ve looked at performing full database backups, tomorrow we’ll move onto looking at transaction log and differential backups.

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

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *