Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

PowerShell changes for database backups in SQL Server 2012


By:   |   Read Comments (1)   |   Related Tips: More > 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

windows powershell 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}
 

windows powershell run a sql server backup using Backup-SqlDatabase

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


Last Update:


next webcast button


next tip button



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

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.



    



Tuesday, December 20, 2011 - 8:31:31 PM - TABridges Back To Top

This code that you posted looks like a lot more work than the regular SQL Server commands.

I'm just sayin'...


Learn more about SQL Server tools