PowerShell changes for database backups in SQL Server 2012

By: Shawn Melton   |   Updated: 2011-12-16   |   Comments (1)   |   Related: > PowerShell

Problem

PowerShell has many features and one set of features is the ability to backup SQL Server databases.  In this tip we look at PowerShell enhancements for SQL Server 2012 to backup SQL Server databases.

Solution

In two previous tips I went over how to do a FULL and LOG backup on all the user databases for an instance with SQL Server PowerShell (SQLPS).  If you have not heard the news already Microsoft has released the first Release Canidate for SQL Server 2012. You can download that here. I wanted to show you how backups can now be done much more easily in SQL 2012 using PowerShell.

One big change the SQL Server Team made was to change SQLPS to a module, instead of the snap-in. So you now import instead of add. To import the SQLPS module simply execute the following command:

Import-Module SQLPS -DisableNameChecking

You will note that after importing the module it will automatically set your location to the SQLSERVER: provider.

With SQL Server 2012 a new cmdlet has been created specifically for backing up a database: Backup-SqlDatabase. The documentation has not made it online as of this writing, so you can simply execute help Backup-SqlDatabase -full to read it.

I have installed SQL Server 2012 as a default instance on a virtual machine. The basics of this command is giving a database name, type of backup to perform, the location and filename for the backup, and then a few optional switches to use if you wish. The "-BackupAction" parameter by default performs a database backup, so unless you want to do a different type of backup you do not have to include it. Although I would suggest doing so for readability. The command below will take a FULL backup of each user database found on my instance. Which the only ones I have are the SSRS databases.

cd .\SQL\GREYSKULL\Default\Databases
foreach($database in (Get-ChildItem)) {
$dbName = $database.Name
Backup-SqlDatabase -Database $dbName -BackupAction Database -BackupFile "E:\temp\$dbName.bak" -Initialize}
 

If the backups take a signifcant amount of time you may notice the progress bar.

As I did in the previous tips, I like to include a date stamp on the filename. To do this you will need to create a new variable for the backup file name so you can combine the $dbName with the variable that holds your current date:

cd .\SQL\GREYSKULL\Default\Databases
$cdate = Get-Date -Format MMddyy
foreach($database in (Get-ChildItem)) { 
$dbName = $database.Name
$bakFile = "E:\temp\" + $dbName + "_" + $cdate + ".bak"
Backup-SqlDatabase -Database $dbName -BackupFile $bakFile -Initialize }
 

If you wanted to do a log backup you would simply need to specify the -BackupAction Log within your backup command. Remeber, in order to do a log backup against a database it must be in FULL or BULK LOGGED recovery model. To run a log backup against every database that IS NOT in SIMPLE recovery mode you can use something like this:

cd .\SQL\GREYSKULL\Default\Databases
$cdate = Get-Date -Format MMddyy_hhmm
foreach($database in (Get-ChildItem | where {$_.RecoveryModel -notmatch 'Simple'})) {
$dbName = $database.Name
$trnFile = "E:\temp\" + $dbName + "_" + $cdate + ".trn"
Backup-SqlDatabase -Database $dbName -BackupAction Log -BackupFile $trnFile -Initialize}
 

Now you would not necessarily do this in your production environment, this is just an example of how you can perform a log backup.

Next Steps

• Download SQL Server 2012 RC0 and start exploring what the next release is going to offer.
• Take a look at the Restore-SqlDatabase cmdlet that is also new in SQL Server 2012.
• Check out other great tips about SQL Server PowerShell.

About the author

Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips