Invoking SQL Server Database Backups with PowerShell

Problem

We all probably know how to initiate a database backup via tools like SQL Server Management Studio. Simply, choose a database, right-click, choose a few options and make it happen. This works great for one or two databases, but what if you need to do this on a recurring basis or need to build this backup step as part of some kind of larger database maintenance orchestration process? One way to do that would be through PowerShell. To be more specific, there are multiple ways to backup a database through PowerShell. Let’s go over two of them.

Solution

Before you can use either of the methods I’m about to go over you’ll first need to ensure you have some prerequisites in order. You’ll either need to have the SQL Server Management Studio software installed or all of these packages.

This will give you the SQLPS module on your local computer which gives you the various PowerShell cmdlets we need to perform various ways to backup our databases.

Invoking T-SQL on the Server

If you’re a T-SQL ninja already and already have scripts to backup your databases, there’s no sense in learning other methods. However, you will need a platform to kick off that T-SQL script which PowerShell can provide. By using PowerShell, you can also create easy-to-use functions that allow you to set various strings in your T-SQL code at run-time rather than having to modify them every time your script runs.

The SQLPS module gives you a cmdlet called Invoke-SQLCmd. This is a cmdlet that, among other things, is a great way to invoke an existing T-SQL script against a SQL server. You simply point it at the server instance, the database you’d like to learn the script on and provide it the query you’d like to run.

Let’s say you need to backup your database to a file. Right now, you need a full backup which, in that case, a tiny bit of T-SQL would do just fine.

BACKUP DATABASE AdventureWorks  
TO DISK = 'C:\AdventureWorks.BAK' 
GO

We could assign each of the snippets to a variable $Query and pass this T-SQL to the server. To do this, you would use Invoke-SqlCmd and pass in the required parameters.

$Server = 'SQLSERVER' 
$Database = 'AdventureWorks' 
$FilePath = 'C:\AdventureWorks.bak' 
$Query = "BACKUP DATABASE $Database TO DISK = '$FilePath'"
Invoke-SqlCmd -ServerInstance $Server -Database $Database -Query $Query

However, you probably don’t want to modify this with a different server, database and file path every time. Wouldn’t it be nice to have a single method to choose either a full or differential backup or change the database name? On top of that, I’m sure your file location would change too. This is a perfect problem for a PowerShell function to solve.

Backup-SqlDatabase

If you’re lucky enough to have SQL Server 2012 or later, you are able to get the Backup-SqlDatabase cmdlet with the SQLPS PowerShell module. This cmdlet essentially is the solution to the function we built earlier. Instead, Microsoft opted to make it easier on us and built a cmdlet of their own to make it easier to backup a database.

Using the Backup-SqlDatabase cmdlet is straightforward. As with our function earlier, we’ll need the server name, the database name and the file where you’d like the backup placed.

$Server = 'SQLSERVER' 
$Database = 'AdventureWorks' 
$FilePath = 'C:\AdventureWorks.bak' 
Backup-SqlDatabase -ServerInstance $Server -Database $Database -BackupFile $FilePath

Notice this time, instead of having to figure out the T-SQL, the logic to actually perform the backup was already built inside of the Backup-SqlDatabase cmdlet. Instead of using the Query parameter, we simply need to pass in a file path with the BackupFile parameter.

If you need to modify the backup options for Backup-SqlDatabase, you can always take a look at the help by running Get-Help Backup-SqlDatabase. This will show you how to change the backup method and many other options.

Next Steps

Check out these other SQL Backup tips.
SQL Server Tutorial – SQL Server BACKUP DATABASE command.
Check out other PowerShell tips.

Resources

Community

Subscribe