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