SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

I Need a Backup and I Need It Now

I Need a Backup ... And I Need It Now

Have you ever wished that creating a backup on the fly was easier and quicker? Whether you do it with the GUI or with T-SQL, you still have to open up Management Studio, connect to the database, and go through the steps. You have to figure out where to put it, give it a name, and so on. An easier way is to have a script ready that you can simply pass a couple of parameters and let it do all of the work for you. You can do this easily enough with a Windows script (what we old-timers like to call DOS when we're feeling nostalgic) to call SQLCmd. You can also do it with Powershell. I'll show you how.

A Powershell Backup Script

This script accepts 3 parameters for the server name, the database name, and the backup path. The backup path parameter is optional. If not provided, the script will use the default backup directory configured for the server. If not provided and the default backup directory returns null, an error will be raised. The backup directory should be set automatically at installation, so this shouldn't happen unless you purposefully delete the configuration.


  1. $Server: [String], Name of server, Required.
  2. $Database: [String], Name of database, Required.
  3. $BackupPath: [String], Backup directory, Optional, Defaults to configured server defualt

A subdirectory with the name of the database will be created for the backup. For example, if you pass in MyDatabase for the database and c:\mssql\bak as the backup path, the backup will be created in c:\mssql\bak\MyDatabase\.

Wow, you posted about something unrelated to database mirroring?

Well, not exactly. This script is really just a small part of my database mirroring automation script. As I'm sure you know, you must create a full backup as one of the steps for setting up database mirroring. So, yes, there is a tie in.

What About Restoring the Backup

Yes, I already have that script written too. I will post it soon. Probably tomorrow or Monday. Come back soon.

Finally, the Script

param (
        [string] $Server,
        [string] $Database,
        [string] $BackupPath

## Path and name used to invoke script
$CUR_SCRIPT = $myinvocation.InvocationName

## Load SMO assemblies
$SMO = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
## Parse out the internal version number
$SMOVer = $SMO.FullName.Split(",")[1].Split("=")[1].Split(".")[0]
## Load SMOExtended if not SQL Server 2005 (9)
if ($SMOVer -ne 9) {

## Check user input, prompt for each value not provided as parameters
if(!$Server) { $Server = read-host "Enter Server Name" }
if(!$Database) { $Database = read-host "Enter Database Name" }
if(!$BackupPath) { $BackupPath = read-host "Enter Backup Path (optional)" }

## Return Help and exit if any required input is missing
if(!$Server -or !$Database) {
        write-host "Usage: $CUR_SCRIPT options:
        string SQL Server Instance
        string Database Name
        string Backup Path (optional)" -f red

## Function to raise error
Function RaisError ([string]$ErrMsg){
        write-host $ErrMsg -f red

## Create server object
$Srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Server

## Get default backup path if not provided
if (!$BackupPath) {
        $BackupPath = $Srv.BackupDirectory

## Make sure backup path exists
if ($BackupPath) {
        $BackupPath = [System.IO.Path]::Combine($BackupPath, $Database)
        [System.IO.Directory]::CreateDirectory($BackupPath) | out-null
} else {
        RaisError "`tUnable to find a backup path"

## Connect to database
$DBase = $Srv.Databases[$Database]

## Create backup name
$BkDate = Get-Date -Format yyyyMMddHHmmss
$BkName = $Database + "_backup_$BkDate.bak"

## Backup the Principal database
$Backup = new-object "Microsoft.SqlServer.Management.Smo.Backup"
$BkFile = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$BkFile.DeviceType = 'File'
$BkFile.Name = [System.IO.Path]::Combine($BackupPath, $BkName)
$BKFileName = $BkFile.Name
$Backup.Database = $Database
$Backup.Action = 'Database'
$Backup.Initialize = 1
$Backup.BackupSetDescription = "Backup of database $Database"
$Backup.BackupSetName = "$Database Backup"
$Backup.PercentCompleteNotification = 5
if (!$error){
        write-host "`tDatabase $Database backed up to $BkFileName" -f green
        return $BkFileName
} else {
        RaisError "`tDatabase $Database backup returned an error."


Posted by Robert Davis on 19 April 2009

Please note that I edited the script slightly to add a return statement near the end.

Posted by Steve Jones on 20 April 2009

Interesting script. I'm assuming this needs Powershell only on your desktop, not the server, correct?

Posted by Robert Davis on 21 April 2009

That is correct. You only need it on the server where the script is running.

Posted by Ben Thul on 29 April 2009

Looking at the way the script is written, it does kind of assume that you're running on the host of the SQL server.  Specifically, when you check to see if the directory provided for the backup exists and try to create it if it doesn't.  The check will happen client side, whereas the SQL Server backup process cares about whether that directory exists client side.  By making the client and server side the same, you would be able to run this script without any problems.

Posted by SQL_Quest on 29 April 2009

I am confused as Steve said that "powershell needs on your desktop and not on your server" but you responded saying "That is correct. You only need it on the server where the script is running."

Does this mean that PS can be just installed on local machine as opposed to installing it on the server. If yes, then this makes life easy to have on the fly backups from any server.

Please clarify if I am understanding wrong.



Posted by Andy on 29 April 2009

Will this script take a COPY_ONLY backup so that you will not break the log chain? This would be useful if you need to take an ad-hoc backup outside of any automated backup schedule and your ad-hoc backup 'disappears'.



Posted by Robert Davis on 29 April 2009

To Ben:  Great point Ben. I need to rethink that line.

To Kinjal: Powershell is creating remote connections to the SQL Server. It's not doing anything on the remote server. So yes, powershell only needs to be on your local machine in this case. It is using SMO for everything which is built in to SQL Server 2005+. SMO is not part of powershell.

To Andy: Yes, you can set the CopyOnly property of the Backup class of SMO. It is a boolean property. I could set it along with the other properties like this:

$Backup.Database = $Database

$Backup.Action = 'Database'

$Backup.Initialize = 1

$Backup.CopyOnly = $true

For clarification, $true and $false are reserved variables used to represent boolean values.

Posted by Robert Davis on 30 April 2009

Additional note to Kinjal:  I just want to point out one more thing. To use SMO with powershell remotely like this, you do have to have the SQL Server 2005+ clinet tools installed on your local server. You don't have to have the database engine, but you do have to at least have the client tools.

Posted by RichardB on 30 April 2009

....and that's easier than writing a simple command into sqlcmd/qa?


Posted by viacoboni on 30 April 2009


You might want to check out sp_ABBackupDb in the article www.sqlservercentral.com/.../2137.  

You need to open up OSQL or Management Studio, but the backup itself is as easy as:

sp_ABBackupDb 'Database1,Database2'

and includes

  - autodetection and use of SQLBackup or Lightspeed

  - auto appending of date/time in Maintenance Plan format

  - by default placement of backups in their own directory under the default backup directory

  - others options including deleting old backups

  - a corresponding sp_ABRestoreDb that really saves work


I must say, I applaud the use of PowerShell and I want to use more of it myself.


Posted by Robert Davis on 30 April 2009

Yes it is easier. For one, with this script, I don't have to provide a file path and name for the backup. And I don't have to know where to put the backup before hand.

Vince, sounds like a very useful procedure. I have procedures like that as well.

Posted by dsully on 18 August 2009

I am getting an error everytime:

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'myServer'. "

At C:\PowerShell\BackupProdDB.ps1:78 char:18

+ $Backup.SqlBackup( <<<< $Srv)

What am I doing wrong?

This will be very useful if I can get it to work. Thanks in advance.

Leave a Comment

Please register or log in to leave a comment.