blue backup key on grey computer keyboard

PowerShell Lets You Back Up SQL Server Your Way

One of the things that makes me really enjoy having PowerShell in my tool belt is the ability to approach a problem from many different angles. Let's take backups, for example.

Related: Backing Up Your Databases

When I back up a SQL Server database, I always back up to a file. The filename I use for the backup always starts with the name of the database, then has either '_db_' or '_tlog_' (depending on whether the backup is a full or a transaction log backup), then a string containing the full date and time, in the format YYYYMMDDHHMMS. I complete the filename with either '.bak' or '.trn', as that's the standard for full and transaction log backups. So, for example, a full backup of the AdventureWorks database taken on January 3, 2014 at 2:37:25PM has a filename of AdventureWorks_db_20140103143725.bak. You don't have to follow my standard, but by looking at my filename I can quickly determine what kind of backup it is and when it was created.


Back before PowerShell, I had T-SQL code that would format the date and time for the backup file name that looked like this.

declare @strDate varchar(30)
set @strDate = CONVERT(varchar, getdate(),112)
set @strDate = @strDate + Left(CONVERT(varchar, getdate(),108),2)
set @strDate = @strDate + SubString(CONVERT(varchar,getdate(),108),4,2)
set @strDate = @strDate + SubString(CONVERT(varchar,getdate(),108),7,2)

Not really very pretty, but T-SQL wasn't designed for efficient string handling. The filename is so much cleaner in PowerShell.

$dt = Get-Date -Format yyyyMMddHHmmss

One quick statement and it's done. Another thing that's a bit complicated, is the location of the backup directory. In T-SQL you're required to read the registry.

declare @BackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT

Personally, I'm not a fan of exposing xp_instance_regread to get the default backup directory. PowerShell, through Server Management Objects (SMO), makes this fairly straightforward.

$svr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $inst
$bdir = $svr.Settings.BackupDirectory

3 Options To Perform PowerShell Backups

So, I naturally gravitate towards PowerShell to perform my backups. From PowerShell, I have three options when deciding how to do the backups. First, there's straight SMO. I'll create a Backup object ($dbbk), then set the properties of the Backup object (including adding the backup file to the Backup Devices collection using the AddDevice() method), then execute the object's SqlBackup() method.

$db = $svr.Databases['AdventureWorks']
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmmss
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 'Database'
$dbbk.BackupSetDescription = "Full backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.MediaDescription = "Disk"
$dbbk.Devices.AddDevice($bdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')

Invoke-SQLCMD Cmdlet

Another option I have is to use the Invoke-SQLCMD cmdlet after building the T-SQL code to perform the backup. I'll use a "here-string" to encode the T-SQL into string format so that it's easily readable. Also, notice that I'm using double-quotes to define the here-string, so I can use PowerShell variables inside the string, and the string parser will substitute the variables with the current value of those variables.

$svnm = $svr.Name
$db = $svr.Databases['AdventureWorks']
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmmss
$bfil = "$bdir\$($dbname)_db_$($dt).bak"
$q = @"
TO  DISK = N'$bfil'
WITH  DESCRIPTION = N'Full backup of $dbname',
NAME = N'$dbname Backup',
Invoke-SQLCmd -ServerInstance $svnm -Database master -Query $q

Use Backup-SqlDatabase Cmdlet

Finally, with the introduction of the SQL Server 2012 SQLPS module, I can use the Backup-SqlDatabase cmdlet, and today that's my preferred method. It's similar to the SMO method, in that the properties are mostly the same, but there are some additional properties in Backup-SqlDatabase that aren't available in SMO.

$svnm = $svr.Name
$db = $svr.Databases['AdventureWorks']
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmmss
$bfil = "$bdir\$($dbname)_db_$($dt).bak"
Backup-SqlDatabase -ServerInstance $svnm -Database $dbname -BackupFile $bfil

Understanding the options always allows you to make the best decisions, and I can tailor a solution specific to my clients' needs.

Related: Set Database Option Properties with PowerShell

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.