One of the most common jokes you’ll hear when talking about the most important tasks for a Database Administrator goes like this – a DBA needs one of two things, a good backup or a good resume. It’s true. If you’re not doing backups, and ensuring that you can recover databases from those backups, you’re exposing yourself and your company to data loss.

The BACKUP DATABASE Transact-SQL command has been around a long time (preceded by the DUMP DATABASE command for those of us around SQL Server long enough to remember it.) It’s still a great way to get your databases protected, but to help us to  move towards more automated ways of getting those everyday tasks done, there are now additional methods available to us. In this article, I’ll be describing, with practical examples, how backups can be scripted effectively from PowerShell via SMO and SQLPS, and how the scripting process has been greatly simplified by using the PowerShell Cmdlets introduced with SQL Server 2012.

In SQL Server 2012, Microsoft added four new cmdlets for Backup and Restore:

• Backup-SqlDatabase
• Restore-SqlDatabase
• Backup-ASDatabase
• Restore-ASDatabase

Why consider scripting backups from an external process.

When the backup process becomes more complex, more and more file-system work needs to be done. These jobs might require the naming and arranging of backups into directories, deleting old backups that are no longer required, copying them offsite, checking backup integrity, writing to logs etc. You might, possibly, need to backup, in one operation, several databases, on different servers even, or copy a database to several servers or VMs for testing.  At some point, a scripted backup and restore needs to be considered, and PowerShell is ideal for this.

Scripting with PowerShell and SMO 

In 2009 I published a whitepaper for Microsoft where I introduced a PowerShell script called backup.ps1 using the Server Management Objects (SMO) library to back up your databases, and since SMO is supported for SQL Server versions 2000 through 2012, it still works.  (Understanding and Using PowerShell Support in SQL Server 2008) In fact, Microsoft hasn’t added a lot of functionality in this area of SMO in SQL Server 2012, so there’s not much new to learn.

In the whitepaper I discussed the SQL Server snapins for PowerShell, and the SQLPS.exe “mini-shell” that’s included in SQL Server 2008 and SQL Server 2008 R2.  While this was the way prescribed by the PowerShell team for extending PowerShell when SQL Server 2008 was introduced, the technology has changed, and the PowerShell team now promotes the “module” model for extending PowerShell.  As a result, PowerShell version 2.0 is a minimum requirement on a server before installing SQL Server 2012, and the SQLPS.exe program has been replaced by a new module called – wait for it – SQLPS. 

Microsoft provided a number of new cmdlets in the SQLPS module, most of which are specific to managing Availability Groups and High Availability/Disaster Recovery.  But besides these, they provided four new cmdlets specific to backup and restore operations – Backup-SqlDatabase, Restore-SqlDatabase, Backup-ASDatabase and Restore-ASDatabase.

 Installing the SQLPS module

When you start up PowerShell and want to work with the SQLPS module, you need to use the Import-Module cmdlet.  Starting with PowerShell version 2.0, Microsoft checks object names during the import process against a list of approved verbs.  Because the verbs Backup and Restore are not in the approved list, you’ll get a message indicating that non-approved verbs exist in the module. You can avoid this error by including the –DisableNameChecking parameter to the Import-Module cmdlet when you import the SQLPS module.  (Note that when you import the SQLPS module your location will be set to the root of the SQL Server provider. This is expected behavior.)

Backing up databases with the Backup-SqlDatabase CmdLet

 Simple Backups with Backup-SQLDatabase

Once you’ve loaded the module you can easily create a backup for a database using a command like this:

There are several ways to call this cmdlet, of course.  Another would be to make use of the provider, navigate to the Databases directory under your SQL Server instance, and use the Get–ChildItem cmdlet to iterate through your databases to back each one up.  Because you’re already connected to your instance you don’t need the –ServerInstance parameter.

At its simplest, if you are you can simply type

..which does a backup to the default backup directory and uses the name of the databases as the name of the backup file. If you need to specify the name of the backup file or any other of the many possible parameters, then you may want to do this..

This will back up each of your user databases to the default backup directory, and use the name of the database and the current date and time of the backup in the backup file name.  (Note that you can use the –Force parameter with the Get–ChildItem cmdlet in the foreach loop to include the system databases, but  just make sure you filter out the tempdb database.)

You could also create a variable containing an SMO Server object and use the –InputObject parameter instead of the –ServerInstance object. Although we’ll show this happening for a single database, this is useful when you have a list of databases in different servers that need to be backed up.

Or, you can use a variable containing the SMO Database object for the target database, and use the -DatabaseObject parameter.

In each of these examples, I’ve used just the minimum of parameters to keep the examples simple. In your code, you should look at the other parameters to get the right backup solution for your needs. So what are these parameters, and how should they be used?

 SQL-Backup: The key parameters

A parameter I would always include is –BackupAction.  The possible values are Database, Files, or Log, and the default value if not included is Database. If you want to do a differential backup, specify the BackupAction as Database, and add the -Incremental parameter. If you want to backup individual files or file groups you’d use the Files option, and of course the Log option allows you to do transaction log backups. For the sake of space, these examples exclude that parameter so they create full database backups.

 For example, I would always include the –CompressionOption On parameter.   I’d also be certain to include the -ConnectionTimeout parameter and set it to a value of 0, because you don’t want your backups terminated because the connection between the script and SQL Server is idle while the backup occurs.

If we expand out the list of parameters to the Backup-SqlDatabase cmdlet from the get-help output for that cmdlet, we get this list:

Backup-SqlDatabase

[-Database] <string>

[-BackupFile] <string[]>]

[-ServerInstance <string[]> ]

[-BackupAction <BackupActionType>]

[-BackupDevice <BackupDeviceItem[]>]

[-BackupSetDescription <string>]

[-BackupSetName <string>]

[-BlockSize <int>]

[-BufferCount <int>]

[-Checksum]

[-CompressionOption <BackupCompressionOptions>]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-CopyOnly]

[-Credential <PSCredential>]

[-DatabaseFile <string[]>]

[-DatabaseFileGroup <string[]>]

[-ExpirationDate <DateTime>]

[-FormatMedia]

[-Incremental]

[-Initialize]

[-LogTruncationType <BackupTruncateLogType>]

[-MaxTransferSize <int>]

[-MediaDescription <string>]

[-MediaName <string>]

[-MirrorDevices <BackupDeviceList[]>]

[-NoRecovery]

[-NoRewind]

[-Passthru]

[-Restart]

[-RetainDays <int>]

[-Script]

[-SkipTapeHeader]

[-UndoFileName <string>]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[<CommonParameters>]

Advantages over SMO

It’s obvious to me that they’ve added functionality to this cmdlet over what is available in SMO.  Something that has been available via Transact-SQL and missing from SMO is the ability to set the block size and buffer count for backups, and they’ve enabled those properties. Here are the equivalent properties and methods available to us via the SMO Backup object:

(This is from the Object Browser in Visual Studio after loading the SMOExtended DLL, where the Backup and Restore objects live.)

Restoring databases with the Restore-SQLDatabase CmdLet

There are a lot of different reasons why we need to restore databases, so there are a lot more options with restores than there are with backups.

The easiest way to demonstrate a restore is to simply restore a database from a full backup, setting the option to overwrite the existing database.

One of the reasons I’ve had to restore databases frequently is to recover data from some table that a user inadvertently deleted, but other transactional changes had subsequently been made that couldn’t be lost.  To accomplish this I’d restore the backup to another named database on the same server, usually with the original database name with the date of the backup appended to the name.  I could then copy the lost data from the backup directly into the original database’s table and drop the copy when all is well again.

Restoring databases with SMO

Here’s how we do this using SMO directly.

One of the potential issues when restoring a copy of an existing database to the same server is physical name conflicts, and to address that you use SMO RelocateFile objects. This is the equivalent of using the WITH MOVE clause in Transact-SQL.  Interestingly, the Restore-SqlDatabase cmdlets requires these same SMO objects to accomplish the same thing.

The only difference between using pure SMO and using the Restore-SqlDatabase cmdlet to accomplish this is how you store and pass the RelocateFile objects.  We need to create an empty collection, which we do right after reading the backup file list, and add each RelocateFile object to the collection, then we call the Restore-SqlDatabase cmdlet.  Here’s just the last part of the same code, using the new cmdlet instead.

In this case, I’ll stick with the pure SMO method, as I think it’s cleaner.

Restoring databases to a point in time

The last example I’ll share involves restoring a database to a point in time. Sometimes the problem that occurred causing you to restore was something that happened at a time you know, and you’re able to decide to bring back the database to that moment.

In this scenario we store all our backup files in the E:\Backup directory on the local server. We have multiple full backups, multiple differential backups, and multiple transaction log backups, including log backups taken after our target point-in-time.  The file names follow the convention DatabaseName_type_datetime.ext, where type is db, diff or tran and ext is either bak or trn.

Now the $fullfile variable contains the file information for the last full backup before the target time, the $difffile variable contains the file information for the last differential backup before the target time, and the $tranfile variable contains file information on all the transaction log backups taken since the differential backup identified in $difffile.

First, we need to restore the full backup, with the replace option and specify no recovery.

Next, we restore the latest differential, again specifying no recovery.

Finally we restore the transaction log backups by looping through the files in the $tranfile variable. If the LastWriteTime property is less than our $restorept variable, then restore with no recovery and go on to the next one. The first log backup that was taken after our restore point is restored with the -ToPointInTime parameter, without the -NoRecovery paramater, and we set our indicator to keep us from attempting to restore any more transaction log backups.

There’s a lot of versatility here, and having direct access to operating system features makes automating these backups and restores much more convenient than attempting to work through Transact-SQL.

 Just for example, here are the SMO properties and methods for the Restore object:

Correspondingly, here is the help text from the Restore-SqlDatabase cmdlet showing the parameters:

Restore-SqlDatabase

[-Database] <string>

[[-BackupFile] <string[]>]

-ServerInstance <string[]>

[-BackupDevice <BackupDeviceItem[]>]

[-BlockSize <int>]

[-BufferCount <int>]

[-Checksum]

[-ClearSuspectPageTable]

[-ConnectionTimeout <int>]

[-ContinueAfterError]

[-Credential <PSCredential>]

[-DatabaseFile <string[]>]

[-DatabaseFileGroup <string[]>]

[-FileNumber <int>]

[-KeepReplication]

[-MaxTransferSize <int>]

[-MediaName <string>]

[-NoRecovery]

[-NoRewind]

[-Offset <Int64[]>]

[-Partial]

[-Passthru]

[-RelocateFile <RelocateFile[]>]

[-ReplaceDatabase]

[-Restart]

[-RestoreAction <RestoreActionType>]

[-RestrictedUser]

[-Script]

[-StandbyFile <string>]

[-StopAtMarkAfterDate <string>]

[-StopAtMarkName <string>]

[-StopBeforeMarkAfterDate <string>]

[-StopBeforeMarkName <string>]

[-ToPointInTime <string>]

[-UnloadTapeAfter]

[-Confirm]

[-WhatIf]

[<CommonParameters>]

Backing up Analysis Services with Backup-ASDatabase

Analysis Services databases need to be backed up as well, even though there are far fewer options to do so.  The first thing you’ll need to do is to load the Analysis Services cmdlets, as they’re in a different module.

Let’s take a look at the parameters for the Backup-ASDatabase cmdlet.

Backup-ASDatabase

[-BackupFile] <string>

[-Name] <string>

[-AllowOverwrite <SwitchParameter>]

[-BackupRemotePartitions <SwitchParameter>]

[-ApplyCompression <SwitchParameter>]

[-FilePassword <SecureString>]

[-Locations <Microsoft.AnalysisServices.BackupLocation[]>]

[-Server <string>]

[-Credentials <PSCredential>]

[<CommonParameters>]

Notice there aren’t options to do differential or transaction log backups here.  A simple backup of the AWDB database looks like this.

If we want to overwrite the backup if it exists already we can add the -AllowOverwrite  parameter, if we want to compress the backup (always a good idea) we can add the -ApplyCompression parameter, and if we want to encrypt the backup we can add the -FilePassword parameter.

Restoring an Analysis Services database with Restore-ASDatabase

Similarly, there aren’t a lot of options when we want to restore an Analysis Services database.

Restore-ASDatabase

[-RestoreFile] <string>

[-Name] <System.String>

[-AllowOverwrite <SwitchParameter>]

[-Locations <Microsoft.AnalysisServices.RestoreLocation[]>]

[-Security <Microsoft.AnalysisServices.RestoreSecurity>]

[-Password <System.SecureString>]

[-StorageLocation <System.String>]

[-Server <string>]

[-Credentials <PSCredential>] [<CommonParameters>]

To restore the AWDB database we can do so like this.

This restores the database and restores the roles and members from the backup as well.

Summary

Backup and restore are critical activities in managing any organizations data. Backup and restore tasks for administering SQL Server are often best scripted using PowerShell and SMO, especially when a DBA needs to do more complex, repeatable, jobs. Where databases must be highly available, for example, it is good practice to create scripts for restoring them, test them thoroughly and then use them for rehearsals. For this, the Backup and Restore cmdlets provided with SQL Server 2012 are ideal for making the task as simple as possible.