Using PowerShell for SQL Server Database Backups

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

Problem

I have seen some of your SQL Server Backup tips.  I am starting to learn PowerShell and want to see how I can use it for common DBA tasks.  How can I create a basic PowerShell script to backup all my user databases?  Check out this tip to learn more.

Solution

With T-SQL, a CURSOR is used to build the BACKUP command and execute it for each database. You can see an excellent example of that process with this tip (Simple script to backup all SQL Server databases). In PowerShell you will use a variable that is populated with all your database names on the instance, and pass each one of those to a ForEach loop. The ForEach loop takes the place of using a CURSOR. The following script will allow you to backup each user database within a single instance. The format of the backup file will be "DBName_YearDayMonth.bak". 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

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

#Grab the database names
$dbname = dir 'SQLSERVER:\SQL\KRINGER\DEFAULT\Databases' | Select Name

#Backup each user database found.
$dbname | foreach { $_.Name.ToString() } | foreach {$bakfile = "$directory" + $_ + "_" + $date + ".bak";
"Backing up Database: $_"; Invoke-Sqlcmd -SuppressProviderContextWarning -Query 
"BACKUP DATABASE $_ TO DISK=N'$bakfile' WITH INIT";
   }

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

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. 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.
3. Set our directory path for the location of the backup files.
4. Pull in our names of the user databases. SQL Server PowerShell hides the system databases so this command will only return our user database. If you want to include the system databaes in the backup, just add "-Force" after the path.
5. Finally we pass each database name through the pipeline to our ForEach loop. I added in the "Backing up Database" part as an indicator. It will show the current DB name that is getting backed up as it goes through them.

Next Steps

• Check out other great tips on SQL Server & PowerShell.
• Check out MSDN and TechNet to get more familar with SQL Server PowerShell.
• To better understand SQL Server Backups read through this TechNet Magazine article by Paul Randal: Understanding SQL Server Backups.
• Check out these SQL Server Backup resources on MSSQLTips:
  • SQL Server Backup and Recovery tips
  • SQL Server Backup Tutorial
  • SQL Server Restore Tutorial

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