Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using PowerShell for SQL Server Database Backups

MSSQLTips author Shawn Melton By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | More > 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:

PowerShell Output of SQL Server Backup Commands

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


Last Update: 9/16/2011


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, October 11, 2011 - 3:40:31 AM - pl80 Read The Tip

I would use PowerShell only when T-SQL is not possible or inefficient.


Friday, September 16, 2011 - 11:10:30 AM - Jeremy Kadlec Read The Tip

Everyone,

Sorry for the editing issue.  The tip has been corrected.

Thank you,
Jeremy Kadlec


Friday, September 16, 2011 - 10:58:50 AM - slarochelle Read The Tip

There is PowerShell code that isn't displayed on the web site.  If you copy the entire block of code and paste it into Notepad or similar, you'll see it goes onto call Invoke-SQLCmd which contains the Backup Database command.

This not the best example of an enterprise level use of Powershell.  Learning Powershell was handy for me when I had to collect some DMV info from ~75 different servers.  It was very easy to load up a list of my servers, and loop over them calling my query, sending the output to one centralized table.

I suppose SSIS could do similar.  But command line is usually more powerful.  We write transact SQL instead of using Access's or even SSMS's graphical query builder right?  PowerShell is analogous to that, IMHO.


Friday, September 16, 2011 - 10:48:03 AM - Shawn Melton Read The Tip

@Stephen

Thanks, I just sent them a correction to see if it will fix it so the complete command will be seen.


Friday, September 16, 2011 - 10:41:37 AM - Stephen Merkel Read The Tip

@Shawn

The code I see on the site ends with creating the filenames for the backup. I understand how its supposed to work, I just think the script got truncated on the site.


Friday, September 16, 2011 - 9:51:54 AM - Simon Kennedy Read The Tip

@Shawn

I know our Network Admin is forever suggesting I use powershell. But every time he suggests something I can show him a t-sql script or functin that does it just as well in the management console.

 

Yes it's another method for doing things in SQL server but for me it just seems like a way for none SQL admins to try to do things SQL server has done perfectly well for a long time now.

 

This example for instance, I can extract a t-sql script in seconds from the backup wizard, run another script to populate the database names and run the thing very easily inside sql server.

 

Yes i can eat my rice with chopsticks if I try very hard, but why give myself all the extra work when i can more efficeintly use a spoon?

 

Years ago a boss of mine asked me how I would change a row of figures on a spreadsheet into a column. I came up with all sorts of automated mathods. He asked me "Why not just type them as a column?"

He then handed me a pen and a pad of paper and said "If you can do it with a piece of paper and a pen, if you can do it the easy way why complicate things?"


Friday, September 16, 2011 - 9:10:58 AM - Shawn Melton Read The Tip

@Simon

PowerShell is just another method of doing things with SQL Server.


Friday, September 16, 2011 - 9:09:33 AM - Shawn Melton Read The Tip

@Stephen: You would need to have SQL Server PowerShell is only available with SQL 2008 and higher. You might also look at installing the SQL Server 2008 R2 SMO and SQL Server Snap-ins available as a stand-alone installation now by Microsoft. Chad Miller wrote up a good post on using it: http://sev17.com/2010/07/making-a-sqlps-module/

 


Friday, September 16, 2011 - 9:01:30 AM - Simon Kennedy Read The Tip

I'm sorry but I don't see the point in using powershell to do jobs that can easily achieved through a maintenance plan or SSIS.

 

Can someone please explain?


Friday, September 16, 2011 - 8:45:56 AM - Stephen Merkel Read The Tip

Uhm. I don't think this is gonna work. looks like we are missing that part of the script that actually executes the backup command?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.