Skip to content

Powershell Basic SQL Backup – T-SQL Tuesday #39


Wayne Sheffield (B|T) is hosting this month’s T-SQL Tuesday and he has asked us to talk about Powershell. He is currently doing a blog series – A Month of Powershell so his topic is not too much of a surprise.

I have dabbled with powershell since it came about and I do like scripting languages in general. However, in the IT world there are always eight ways to do everything and I don’t always turn to powershell for SQL tasks. There is one area that I created some quick scripts which I can modify easily for a lot of situations – Backups and Restores. The same things could be accomplished with T-SQL scripts, but I can easily use powershell to read the file system and do complex conditional statements across SQL instances. I have a very basic powershell script that uses the new Backup-SQLDatabase cmdlet that I can expand upon as needed. The Backup-SQLDatabase cmdlet was added in SQL Server 2012 and is essentially a wrapper that is calling the .Net SQL Management Object (SMO) for a SQL Backup.

I know when I am trying to learn something in powershell I like to see a simple version first and then as I learn I can expand on the script. That isn’t to say that I don’t borrow from other people’s scripts when they have already figured out how something works, but I know I learn more I create something on my own. With that in mind I have a simple script that you don’t need to be a powershell wizard to be able to run. The script below is very basic, but it could be done in even a single command. All I have added is some formatting so I can get a descriptive filename and media set name.

param([string]$db ,[string]$Server, [string]$dest);

$timestamp = Get-Date -format yyyy_MM_dd_HHmmss;
$Dest = $Dest + "\" + $db + "_full_" + $timestamp + ".bak", "File";
$BackupSetName = "Full backup of " + $db + " " + $timestamp;

Backup-SqlDatabase -ServerInstance $Server -Database $db -BackupFile $Dest -CopyOnly -Initialize -Checksum -CompressionOption On -BackupSetName $BackupSetName

In the first line I am using Parameters so that I can call this script from the command line or more often a .bat file and just enter the database, SQL Server Instance, and the file location for the backup. In the second line I am grabbing the Date and formatting so I can use it as part of the filename.

Next I format the destination file name and the BackupSetName. I could do this all on one line or even when I pass the parameter to the cmdlet, but this makes it easier to read.

Lastly I call the Backup-SQLDatabse cmdlet to perform the backup. I pass in several parameters to get the backup that I want. If you are accustomed  to using the T-SQL backup commands these parameters should all look familiar.

  • -ServerInstance  = The SQL Server instance where I am performing the backup. Since the server is a parameter I can easily run this script from any machine that has SQL Server 2012 installed (as long as network permissions allow)
  • -Database = The database to be backed up. The command can also be used to do a log backup or a file backup, but I typically am doing the entire database when doing these adhoc backups.
  • -BackupFile = The filepath and name for the file to backup to.
  • -CopyOnly = Typically when I am using a script like this it is adhoc and I don’t want to break the backup chain I have with my normal maintenance scripts.
  • -Initialize = A flag that says to overwrite the backup file if it exists.
  • -Checksum = A flag that sets a checksum value for validation.
  • -CompressionOption – Creates a compressed file which is useful since the next step after these types of backups is usually to restore to another database. This way the file I am copying around is not as large.
  • -BackupSetName = A descriptive name for the backup media set that is recorded in the msdb.dbo.backupmediaset system table.

To run the script I save it in the file c:\PSScripts\bu_AdHoc.ps1 and I use the following command:

sqlps c:\PSScripts\bu_AdHoc.ps1 -db Testing -Server DemoServer -Dest C:\Backups

As I said this is the basic version of this script and I can expand on it do things like backup up only at certain times or only if there are no backup files within a certain timeframe. I can easily loop through all of the databases on a server or on a list of servers passed in from a file. Or other options that I haven’t thought of yet.

An easier to read version of the script can be found in the Code section under Basic Powershell SQL Backup 2012. If you are still running SQL Server 2008 (as I am on most of the machines I work with) I have a version of the script that calles the SMO objects directly – Basic Powershell SQL Backup 2008.


From → SQL Server

One Comment

Trackbacks & Pingbacks

  1. T-SQL Tuesday #39 – Wrapup | Wayne Sheffield

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s