Using PowerShell for SQL Server Transaction Log Backups


By:   |   Updated: 2011-09-27   |   Comments   |   Related: 1 | 2 | More > PowerShell


Problem

How can I use PowerShell to do a Transaction Log backup for SQL Server databases? In this tip we look at an alternative way of creating transaction log backups using a PowerShell script.  You can also refer to this tip that shows how to do a SQL Server database backup using PowerShell.

Solution

The script below makes use of variables within SQL Server PowerShell. We can populate a variable with the database names we want a log backup executed against. This variable will be filtered to only include those databases in FULL or BULK-LOGGED recovery mode. The naming convention of the log backup file will be "DBName_YearDayMonth_HourMinSec.trn". In this example the directory is set to "C:\TEMP", you will need to change this to your desired location.

#load SQL snap-in
Add-PSSnapin *SQL*

#pull the current date
$date = Get-Date -Format yyyyddMM_hhmmss

#set location for the backup files
$directory = "C:\temp\"

#Grab the database names, excluding those in Simple Recovery Model
$dbname = dir 'SQLSERVER:\SQL\KRINGER\DEFAULT\Databases' | where {$_.RecoveryModel -notmatch "Simple"} | Select Name

#Backup each log of user database found, not in SIMPLE recovery.
$dbname | foreach { $_.Name.ToString() } | foreach { $bakfile = "$directory" + $_ + "_" + $date + ".trn"; "Backing up LOG of Database: $_"; Invoke-Sqlcmd -SuppressProviderContextWarning -Query "BACKUP LOG $_ TO DISK=N'$bakfile' WITH INIT";}

Your output should look similar to this after you have executed the script:

powershell sql server transaction log backups

The "-SuppressProviderContextWarning" is used to suppress a warning message that shows up each time you run the Invoke-Sqlcmd cmdlet. You can also use "-IgnoreProviderContext" as well. Also, the "$_.Name.ToString()" is for formatting the database name so it agrees with the BACKUP command. If you leave that out you will see what I am talking about. The object type of the database name does not agree with the BACKUP command and this is the method I came up with for getting it to work.

The script takes you through the following steps:

  1. Load the SQL Server Snap-ins.
  2. Grab our timestamp to use on our filename. In order to add the date to our backup file name we first have to grab it in the format we want and store it in a variable. Using the "-Format" allows us to specifically get it as YearDayMonth_HourMinutesSeconds.
  3. Set our directory path for the location of the backup files.
  4. Pull in our names of the user databases that are only in FULL or BULK_LOGGED recovery modes.
  5. Finally we pass each database name through the pipeline to our foreach loop. I added in the "Backing up LOG of Database" part as an indicator. It will show the current DB name that is getting backed up as it goes through them.
Next Steps


Last Updated: 2011-09-27


get scripts

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





Comments For This Article





download





Recommended Reading

Using PowerShell for SQL Server Database Backups

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server








get free sql tips
agree to terms


Learn more about SQL Server tools