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.

Parameters:

$Server: [String], Name of server, Required.

$Database: [String], Name of database, Required.

$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
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$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) {
        [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")|out-null
}

## 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
        exit
}

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

## 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.Devices.Add($BkFile)
$Backup.Database = $Database
$Backup.Action = 'Database'
$Backup.Initialize = 1
$Backup.BackupSetDescription = "Backup of database $Database"
$Backup.BackupSetName = "$Database Backup"
$Backup.PercentCompleteNotification = 5
$Backup.SqlBackup($Srv)
if (!$error){
        write-host "`tDatabase $Database backed up to $BkFileName" -f green
        return $BkFileName
} else {
        RaisError "`tDatabase $Database backup returned an error."
}

I Need a Backup and I Need It Now