Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Invoking SQL Server Database Backups with PowerShell


By:   |   Last Updated: 2016-03-22   |   Comments   |   Related Tips: More > Backup

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


Last Updated: 2016-03-22


get scripts

next tip button



About the author
MSSQLTips author Adam Bertram Adam Bertram is an independent consultant, technical writer, trainer, and presenter. Adam specializes in consulting and evangelizing all things IT automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools