Backup SQL Server Databases with a Windows PowerShell Script

By:   |   Comments (19)   |   Related: > PowerShell


Problem

In a previous tip on Backup and Restore SQL Server databases programmatically with SMO, you've seen how you can use Windows PowerShell to backup and restore SQL Server databases. In this tip, I will cover how to use Windows PowerShell to generate SQL Server backups.

Solution

Generating and maintaining backups are the most important tasks that any DBA has to fulfill. And, as mentioned in the previous tip, SMO can be used to backup and restore a database. There are different ways to backup a database, depending on company policies on disaster recovery. We will be introducing a new SMO namespace that will allow us to create the Backup object. This is an added line in our previous PowerShell scripts to create a new instance of the Backup object.

$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
 

The first property of the Backup object that we need to assign is the name of the database that we need to do a backup on. This is defined by the Database property of the Backup object. The code snippet below defines a Database property for the Backup object to perform a backup on the Northwind database.

$dbBackup.Database = "Northwind"
 

Whenever you generate or create backups, SMO considers this as a Device. This requires us to add the Backup file to the Devices collection of the Backup object, specifying the DeviceType as File. The first parameter in the AddDevice method of the Devices collection is the file name of the backup file with the full path as stored in the file system whereas the second parameter is the DeviceType. While SQL Server supports the use of devices, I normally recommend using the file system to easily identify the backup sets based on the file name using the proper naming convention (one thing you would not want to be worried about during disaster recovery is scrambling to find the valid backup sets).

$dbBackup.Devices.AddDevice("yourDBbackupFile.bak", "File")
  

The next important property of the Backup object that we are interested in is the Action property. The Action property sets the type of backup to be performed - Database, Log or File. The default value of the Action property is set to Database. This property is of importance based on the definition of our service level agreement as you don't just generate backups without understanding the defined SLAs for a specific database or application. For our example, we will define a FULL database backup

$dbBackup.Action = "Database"
  

Once we have defined the properties of our Backup object, it's just a matter of calling the SqlBackup method of the Backup object. The SqlBackup method performs the backup operation as defined by the properties you have set in the Backup object. It accepts a parameter of type Server which represents the SQL Server instance that you have defined as the source of the backup operation.

$dbBackup.SqlBackup($s)
  

Combining the code snippets above, here is a working Windows PowerShell script to perform a FULL database backup against the Northwind database, storing the backup file in your file system.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1" 

#Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace 
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") 

#Set the Database property to Northwind 
$dbBackup.Database = "Northwind" 

#Add the backup file to the Devices collection and specify File as the backup type 
$dbBackup.Devices.AddDevice("D:\PSScripts\backups\NWind_FULL.bak", "File")

#Specify the Action property to generate a FULL backup 
$dbBackup.Action="Database"

#Call the SqlBackup method to generate the backup 
$dbBackup.SqlBackup($s)
 

Now, since you won't be performing backups of just a single database, it would be better if we loop the entire script in a For-Each cmdlet iterating thru the Databases collection of the Server object.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$bkdir = "D:\PSScripts\backups" #We define the folder path as a variable 
$dbs = $s.Databases
foreach ($db in $dbs) 
{
     if($db.Name -ne "tempdb") #We don't want to backup the tempdb database 
     {
     $dbname = $db.Name
     $dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp 
     $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
     $dbBackup.Action = "Database"
     $dbBackup.Database = $dbname
     $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
     $dbBackup.SqlBackup($s)
     }
}
 

While my only condition for generating backups is to exclude the tempdb database, you can include other conditions such as querying the database property if it is used for database mirroring (IsMirroringEnabled property) or specifying database names if you are sure enough that you won't be needing backups for them. While not really necessary, you also might want to include other Backup object properties such as BackupSetName, BackupSetDescription, and MediaDescription as they can be of help in case you wanted to find out the contents of your backup sets.

NOTE: If you have SQL Server 2008 Client Tools installed on your workstation where you intend to run this PowerShell script, it is important that you add a reference to the Microsoft.SqlServer.SmoExtended namespace. The Backup object and a few other objects were moved from the Microsoft.SqlServer.Smo to the Microsoft.SqlServer.SmoExtended. In the example I provided, I have SQL Server 2005 and SQL Server 2008 running on my machine, thus, requiring that I add a reference to the Microsoft.SqlServer.SmoExtended namespace. SQL Server MVP Allen White has blogged about Loading SMO Assemblies in PowerShell and explains using a custom script to load appropriate SMO assemblies depending on the SQL Server version.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")| out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")| out-null
 

Generating Differential and Log Backups

Depending on your service level agreement, you would also need to define either Differential or Log backups to complement your Full backups. This can be done by changing the Action property of the Backup object to either Log for Log backups or setting the Incremental property of the Backup object to a value of 1. Below is a code snippet for generating Log backups, simply by changing the Action property.

$dbBackup.Action = "Log"

Note that you can only do Log backups on databases that are not configured to use the SIMPLE recovery model. This means that your condition for generating Log backups would include a check on the RecoveryModel property

if($db.RecoveryModel -ne 3) #Don't issue Log backups for DBs with RecoveryModel=3 or SIMPLE 
{
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm #Create a file name based on the timestamp 
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Log"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_log_" + $dt + ".trn", "File")
$dbBackup.SqlBackup($s)
}

For Differential backups, you would need to replace the Action property with the Incremental property and set the value to 1

if($db.Name -ne "tempdb") 
{
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm #Create a file name based on the timestamp 
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Incremental = 1 
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_diff_" + $dt + ".bak", "File")
$dbBackup.SqlBackup($s)
} 

Your backup strategies, depending on your service level agreement, will definitely include a combination of either Full and Differential and/or Log backups.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

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

View all my tips



Comments For This Article




Tuesday, August 20, 2013 - 3:00:15 PM - bass_player Back To Top (26396)

Did you restart the SQL Server service? Plus, a simple TELNET test on the port number that SQL Server Express is listening on will tell you if the port is opened. Check the SQL Server error log for the IP address and the port number that SQL Server Express is listening on


Tuesday, August 20, 2013 - 12:42:01 PM - Patrick B Back To Top (26391)

@bass_player

TCP/IP Is already enabled. I have tried specifying the database by IP address and by it's UNC.


Tuesday, August 20, 2013 - 11:51:40 AM - bass_player Back To Top (26389)

Try these steps

1. Open up the SQL Server Configuration Manager. Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager.

2. Expand the SQL Server Network Configuration node and select the Protocols for SQLEXPRESS

3. Enable TCP/IP by right-clicking and selecting Enable, then OK.

4. Click on the SQL Server Services node and in the right panel right-click SQL Server (EXPRESS) and select restart to restart the service.

5. Right-click on the SQL Server Browser and select start to start the browser service if it isn't started already. This will allow you to access the SQL Express instance by the computer name.


Tuesday, August 20, 2013 - 8:45:47 AM - Patrick B Back To Top (26386)

I am trying to get this to work on a remote instance of SQL Server Express 2008 R2.  I have SQL Manegement Tools for SQL 2005 installed on my local machine.  I am able to create the new object but it will not connect to my remote server.  

 

First question, is there a setting I need to enable to allow remote connections or is that even possible with a SQL Server Express 208 R2?  


Monday, July 29, 2013 - 6:41:31 PM - Mark R Back To Top (26053)

I took the code listed in the box right under "it would be better if we loop the entire script in a For-Each cmdlet iterating thru the Databases collection of the Server object." and attempted to run that under powershell.   The below is the resulting errors which look like to a 30+ year UNIX vet as "you didn't load a series of libraries/this is one of them thar object-driven shells and you didn't load the correct objects".   Given this is my 1st attempt at powershell, my knowledge of what should/should not be pre-loaded is lacking.   

Assume a new Windows 8 box, Microsoft 2012 SQL server and someone who's never touched Powershell.  So what are the other magic incantations to make the script you give above work?  

As taken from the above:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$bkdir = "c:\Backups SQL" #We define the folder path as a variable
$dbs = $s.Databases
foreach ($db in $dbs)
{
     if($db.Name -ne "tempdb") #We don't want to backup the tempdb database
     {
     $dbname = $db.Name
     $dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp
     $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
     $dbBackup.Action = "Database"
     $dbBackup.Database = $dbname
     $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")
     $dbBackup.SqlBackup($s)
     }
}

The Output when at a powershell commandline I type ./backuppowershell.ps1

new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure the assembly containing this type
is loaded.
At C:\users\ejl\Desktop\powershellbackup.ps1:12 char:18
+      $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
+                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Property 'Action' cannot be found on this object; make sure it exists and is settable.
At C:\users\ejl\Desktop\powershellbackup.ps1:13 char:6
+      $dbBackup.Action = "Database"
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

Property 'Database' cannot be found on this object; make sure it exists and is settable.
At C:\users\ejl\Desktop\powershellbackup.ps1:14 char:6
+      $dbBackup.Database = $dbname
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At C:\users\ejl\Desktop\powershellbackup.ps1:15 char:6
+      $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\users\ejl\Desktop\powershellbackup.ps1:16 char:6
+      $dbBackup.SqlBackup($s)
+      ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull


Saturday, February 16, 2013 - 11:07:09 AM - sundar Back To Top (22179)

Can you some helpme regarding stop sql backupI couldnt see any where in job scheduler.Its running everyday.I want to stop via powershell commandmode


Saturday, November 20, 2010 - 7:27:27 AM - Repriser Back To Top (10385)

I have been programming for 20+ years using more than a few languages. T-SQL is an easy one but tedious because debugging tool is not so good when it comes to dynamic SQL.

Power-shell has none programming ability, unless some evidence can convince me. You cannot just copy a script and apply to your scenario without changing. So if you want to administer SQL Server database, learn to master it or stay away. Practice on DEV on your PC, not production host.


Saturday, November 20, 2010 - 7:21:33 AM - Repriser Back To Top (10384)

People who don't know SQL Server should leave it to professional. Giving a power-saw to a monkey is not a right thing to do.

The reason SQL Server world is so often messed up is everybody thinks they can monkey SQL Server until a day they cannot handle any more. When you call an ER doctor, it might be too late. Management does not undertand this concept because many are not technical.

On Oracle side, I have not seen many scenarios that management will ask "Jose, you fix it.". Jose did something replies "It is fixed."


Friday, November 19, 2010 - 11:48:06 PM - bass_player Back To Top (10382)

Hi jwong,

I totally agree with you on using TSQL with performing backups. However, this is not "reinventing bicycle and worse." Unfortunately, the reality out  there is that there are accidental SQL Server DBAs who are either systems administrators that are given the task to take care of their SQL Server instances or developers who access their SQL Server databases. This series of articles on Windows PowerShell was targetted at those system administrators who do not know any TSQL at all but are tasked to take care of SQL Server instances. They may know a few scripting languages like VBScript, Perl or Windows PowerShell. This is an attempt to helping those system administrators do their jobs better. And with Windows PowerShell becoming the scripting language of choice for anything running on top of Windows (SharePoint, Exchange, System Center, SQL Server, etc.) this becomes a way to understand the concepts and apply the knowledge to manage any Microsoft server product that supports Windows PowerShell


Wednesday, November 10, 2010 - 2:56:01 PM - jwong Back To Top (10353)

Check this web page, email me what you need in T-SQL. I bet I already have one.

http://usa.redirectme.net/repriser/sqlserverpub.html

http://usa.redirectme.net/repriser/

 

 


Wednesday, November 10, 2010 - 2:48:37 PM - jwong Back To Top (10352)

This is re-inventing bicycle and worse. I have dynamic T-SQL script as a SP that takes hostname, instancename, database as input to backup. Not hard-coded as this script.


Wednesday, November 10, 2010 - 2:44:57 PM - jwong Back To Top (10351)

You can do this and better with T-SQL. There are a lot of already tested T-SQL available to download.


Thursday, October 22, 2009 - 8:45:51 AM - admin Back To Top (4279)

Bass_Player,

Thank you for the insight.

Thank you,
The MSSQLTips Team


Tuesday, October 20, 2009 - 6:51:10 PM - bass_player Back To Top (4268)

My opinion is that TSQL will always be there and will never be replaced as SQL is the language for the database. As far as installation is concerned, Windows PowerShell is already included as a feature in Windows Server 2008 and Windows 7. You just need to install that feature to enable the scripting engine.Still, not too many customers have had Windows PowerShell enabled on their Windows Server 2008 installations simply because of adoption. If server administrators already have a lot of VBScript scripts that they use for managing servers, it would be quite difficult to convince them to rewrite those scripts in PowerShell.

As for Exchange Server 2007, the entire server was built with Windows PowerShell in mind. In fact, the Exchange Management Interface was designed in such a way that all the mouse-clicks and menu-clicks are actually calls to Exchange-PowerShell cmdlets. This is Microsoft's direction by defining this in their common engineering criteria


Tuesday, October 20, 2009 - 8:56:04 AM - admin Back To Top (4262)

Bass_Player,

The installation aspect is interesting. 

How do you think Microsoft is going to treat that issue (i.e. installation)?  Do you think it is going to come to a point where T-SQL is just replaced with SMO or PowerShell in an upcoming version of SQL Server?  Or do you think it will be a gradual shift with T-SQL still available?

Do you know what happened with Exchange 2007?  How is PowerShell used with that platform?

Thank you,
The MSSQLTips Team


Tuesday, October 20, 2009 - 8:51:15 AM - admin Back To Top (4261)

Alan,

Thank you for the insight on how you work with your backups based on size.

Maybe a tip on how your Configuration Management Team is using PowerShell would benefit the community.

Just let us know if you are interested.

Thank you,
The MSSQLTips Team


Tuesday, October 20, 2009 - 8:44:27 AM - bass_player Back To Top (4260)

Unfortunately, not too many people from the community have shifted fully to PowerShell for managing SQL Server. There is still this notion of clinging on to old habits as TSQL is still the way to go for managing SQL Server. The main barrier to using PowerShell for managing SQL Server is installing it. Many customers don't want anything installed on the servers other than what is needed. This is very important for DBAs managing servers remotely via VPN connections as security is another hoop to skip. While not designed to be a replacement to manage SQL Servers, at least DBAs and IT Pros wh oare tasked to manage SQL Server will have other options available.


Tuesday, October 20, 2009 - 8:40:27 AM - --cranfield Back To Top (4259)

We still use native backup scripts as scheduled jobs.  For databases > 100GB we use Litespeed, also scripts scheduled as jobs.

I see no reason at the moment to use Powershell or SMO for backups from a production support point of view. We keep it simple.

Our Release Configuration management team are starting to use Powershell for their build scripts instead of traditional osql/sqlcmd batch files.


Tuesday, October 20, 2009 - 8:34:59 AM - admin Back To Top (4257)

Edwin,

Thank you for this tip as another alternative to perform SQL Server database backups.  It is always good to have options.

How many folks from the community have shifted SQL Server database backups from tradition T-SQL scripts to PowerShell or SMO?  Is it better or worse?  Have either of these languages become your new standard for backup\restore related tasks?

Thank you,
The MSSQLTips Team















get free sql tips
agree to terms