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