Automate SQL Server Express Backups with Powershell

Last week I had the honor of presenting at TechEd Europe a session titled “Deploying Microsoft SQL Server 2012 Using Windows Server Core“. Presenting that session gave me the opportunity to learn about administering SQL server using PowerShell, specifically how to administer an instance of SQL Server running on Server Core (PRO TIP: it’s the same as administering an instance that is NOT running on Server Core). In the session I talked about how I am not a person that would simply rewrite existing scripts (i.e., go from SQLCMD to PowerShell) just because I could. I always needed a reason, a tipping point if you will. I like to call that the “SQL Sugar”.

I found a case of SQL Sugar at TechEd: Windows PowerShell ISE 3.0. If you don’t have it yet, go and get it now. I’ll wait. Done? Great, let’s continue.

Recently I had the need to schedule some database backups for an instance of SQL Express. As I reached into my toolbox I was suddenly hit with a bit of reality: SQL Express does not come with SQL Agent. (As an aside, if you use SSMS 2012 you no longer even see the SQL Agent icons and folder, nor do you see SQL Agent listed as a service anywhere, and no I don’t know why this took Microsoft over seven years to fix).

So, I needed to schedule backups of all databases on an instance for which there was no SQL Agent. Of course I can take my existing T-SQL scripts and modify them to run from Windows Task Scheduler in order to get the job done, but I decided that this was the tipping point for me to start building out some PowerShell scripts. [I probably would not have felt that way if it was not for the new ISE, warrants mentioning.]

I mashed together a quick PowerShell script that you can use to take backups of your SQL instance. It takes two parameters, the first is the name of the instance and the second is the directory you want to use for your backups (you can leave it blank and it should just put the backups in the default backup folder for your instance). Feel free to use or modify as you see fit. Also, my disclaimer:

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code. 

# backup_full.ps1
# Full backup for database for specified SQL instance
#
# Change log:
# July 3, 2012: Thomas LaRock, https://thomaslarock.com
# Initial Version
# Get the SQL Server instance name from the command line
# leaving $dest null will result in default backup path being used
param(
    [string]$inst=$null,
    [string]$dest=$null
    )
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
    }
# Handle any errors that occur
Function Error_Handler {
Write-Host "Error Category: " + $error[0].CategoryInfo.Category
Write-Host " Error Object: " + $error[0].TargetObject
Write-Host " Error Message: " + $error[0].Exception.Message
Write-Host " Error Message: " + $error[0].FullyQualifiedErrorId
    }
Trap {
# Handle the error
    Error_Handler;
# End the script.
    break
    }

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

# If missing set default backup directory

If ($dest -eq "")
    { $dest = $inst.Settings.BackupDirectory + "\" };
    Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
    # Full-backup for every database
    foreach ($db in $srv.Databases) {
    If($db.Name -ne "tempdb") # No need to backup TempDB {
        $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;
        $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");
        $backup.Action = "Database";
        $backup.Database = $db.Name;
        $backup.Devices.AddDevice($dest + $db.Name + "_full_" + $timestamp + ".bak", "File");
        $backup.BackupSetDescription = "Full backup of " + $db.Name + " " + $timestamp;
        $backup.Incremental = 0;
        # Starting full backup process.
        $backup.SqlBackup($srv);
        };
    };
Write-Output ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));

I can run that script from Windows Task Scheduler on a SQL Express instance because…well…Task Scheduler comes cheap and does what I need it to do: run a script on scheduled basis. Now, creating backups is good, but cleaning up after yourself is better. I will point you to this script by Allen White (blog | @sqlrunr), since it is what I use anyway.

Now for the SQL Sugar: the new Windows PowerShell ISE. With the new ISE I get a lot of increased functionality such as improved intellisense and a command text window that lest me easily insert commands. I love showing off the new ISE to everyone, see how shiny it is:

As you type in the ISE you get intellisense, which makes finding what you want a LOT easier than always relying on “Get-Help” all day long:

And the command text window is just a wonderful gift:

This has reduced the amount of time it takes for me to build out PowerShell scripts. In fact, I was able to put together an updated script for backing up my instance of SQL Express in only a few minutes thanks to the new ISE allowing me to discover the Backup-SqlDatabase cmdlet.

Here is the updated version:

# backup_AllDb.ps1
# Full backup for database for specified SQL instance
#
# Change log:
# July 3, 2012: Thomas LaRock, https://thomaslarock.com
# Initial Version
# Get the SQL Server instance name from the command line
# leaving $dest null will result in default backup path being used
param(
    [string]$inst=$null,
    [string]$dest=$null
    )

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
    }

# Handle any errors that occur
Function Error_Handler {
    Write-Host "Error Category: " + $error[0].CategoryInfo.Category
    Write-Host " Error Object: " + $error[0].TargetObject
    Write-Host " Error Message: " + $error[0].Exception.Message
    Write-Host " Error Message: " + $error[0].FullyQualifiedErrorId
    }

Trap {
    # Handle the error
    Error_Handler;
    # End the script.
    break
    }

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

# If missing set default backup directory.
If ($dest -eq "")
    { $dest = $inst.Settings.BackupDirectory + "\" };
    Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
    cd SQLSERVER:\SQL\$inst\Databases
    
    #start full backups
    foreach($database in (Get-ChildItem -Force)) {
        $dbName = $database.Name
        if ($dbName -ne "tempdb") {
            $timestamp = Get-Date -Format MMddyy
            $bakFile = $dest + $dbName + "_full_" + $timestamp + ".bak"
            Backup-SqlDatabase -Database $dbName -Initialize -BackupFile $bakFile
        }
    }
Write-Output ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));

There’s not much difference, really, just four lines or so of code, but I like the format here because it is easier for me to understand what is being done. It is also easier for me to make changes to the backup command by adding in the necessary parameters right there on the one line. Besides the backup-SqlDatabase cmdlet I also want to point out the -Force parameter in the Get-Childitem line. I need that parameter in order to get the system databases listed, otherwise we would only be backing up the user databases. You can see this for yourself by connecting to your SQL instance and running this:

PS SQLSERVER:\SQL\[instance name]\databases> Get-ChildItem

and then comparing the output to this:

PS SQLSERVER:\SQL\[instance name]\databases> Get-ChildItem -Force

Bottom line, I am enjoying PowerShell more now that I have the new ISE. I find it takes less time for me to complete what appear to be very basic tasks. I still would not advocate that you rewrite existing scripts just because you can, you still need to find a good reason to invest the time in rewriting everything you have built. But once you start using PowerShell you will suddenly realize all of the great things it can do for you and you are going to want to start rewriting things anyway!