Day 5 of 31 days of SQL Server Backup and Restore using PowerShell: File and Filegroup backups

So far we’ve only looked at backup types supported by the Maintenance Plans (Full, Differential and Log), now we’re going to start going past those and look at File and FileGroup backups. Normally to perform these you’d need a 3rd party tool or you own home rolled SQL scripts.

The script examples are based on using the following database, with 3 filegroups, 1 of which (tertiary) is readonly:

USE [master]
GO

CREATE DATABASE [fg_test]
 ON  PRIMARY
( NAME = N'fg_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg_test.mdf' ),
 FILEGROUP [secondary]
( NAME = N'fg_test_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg_test_2.ndf' ),
( NAME = N'fg_test_2a', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg_test_2a.ndf' ),
 FILEGROUP [tertiary]
( NAME = N'fg_test_3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg_test_3.ndf' )
 LOG ON
( NAME = N'fg_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\fg_test_log.ldf');
GO

alter database fg_test modify filegroup tertiary readonly;
go

Using SMO, to backup up a single filegroup you use the Add method of the Backup object’s DatabaseFileGroup property, and everything else stays the same:

Import-Module "SQLPS" -DisableNameChecking

$ServerName = "WIN-C0BP65U3D4G"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

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

$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup
$Backup.Action = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Files
$Backup.BackupSetDescription = "Filegroup Backup of Filegroup "+$FileGroupName+" of "+$Db.Name
$Backup.Database = $db.Name
$backup.DatabaseFileGroups.add($FileGroupName)

$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)

The line that does the work is:

$Backup.DatabaseFileGroups.Add($FileGroupName)

If you want to backup multiple FileGroups then you can call the method multiple times:

$Backup.DatabaseFileGroups.Add("primary")
$Backup.DatabaseFileGroups.Add("secondary")

Or you can pass them in via the AddRange operator:

$Backup.DatabaseFileGroups.AddRange(("primary","secondary"))

It can also be done with a loop if you can identify which filegroups you want to backup. For example, if you wanted to backup all non readonly filegroups you could replace the line with this script snippet:

foreach ($fg in $db.FileGroups | where-object {$_.ReadOnly -eq $FALSE}){
    $Backup.DatabaseFileGroups.Add($fg.Name)
}

This is one of the cases where the Backup-SQLDatabase is slightly harder to use. To replicate the first SMO example you’d use:

Import-Module SQLPS -DisableNameChecking

Backup-SqlDatabase -ServerInstance WIN-C0BP65U3D4G -Database fg_test  -DatabaseFileGroup "secondary" -BackupFile "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"

For the second:

Import-Module SQLPS -DisableNameChecking

Backup-SqlDatabase -ServerInstance WIN-C0BP65U3D4G -Database fg_test  -DatabaseFileGroup "primary","secondary" -BackupFile "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"

Note passing in the filegroup names as a comma separated list of strings.

And for the looped version:

Import-Module "SQLPS" -DisableNameChecking

$ServerName = "WIN-C0BP65U3D4G"
$SQLSvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerName)

$Db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database
$Db = $SQLSvr.Databases.Item("fg_test")
$Filegroup = ""
$i=0
foreach ($fg in $db.FileGroups | where-object {$_.ReadOnly -eq $FALSE}){
    if ($i -eq 0){
        $Filegroup = $FileGroup+"`"$fg.Name`""
    }else{
        $Filegroup = $FileGroup+",`"$fg.Name`""
    }
}
Backup-SqlDatabase -ServerInstance WIN-C0BP65U3D4G -Database fg_test  -DatabaseFileGroup $Filegroup -BackupFile "c:\psbackups\"+$Db.Name+"_"+[DateTime]::Now.ToString("yyyyMMdd_HHmmss")+".bak"

Here we still have to build the connection to the SQL Server instance so we can get the information about the Filegroups. And then loop through it building up the correct string format to pass in as a parameter. I use a simple counter $i to keep track of how many filegroups we’ve found and to ensure the “,”s only go where they’re needed.

Database file backups work in exactly the same way, but using the $Backup.Files.Add() method or the -DatabaseFile parameter.

Tomorrow we’ll be looking at redirecting the backup files.

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 *