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.

Pre-PowerShell

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'SoftwareMicrosoftMSSQLServerMSSQLServer', 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')$dbbk.SqlBackup($svr)

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 = @"BACKUP DATABASE [$dbname]TO  DISK = N'$bfil'WITH  DESCRIPTION = N'Full backup of $dbname',NOFORMAT, NOINIT,MEDIADESCRIPTION = N'Disk',NAME = N'$dbname Backup',NOSKIP, REWIND, NOUNLOAD,  STATS = 10"@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