Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server Express Backups and Purge Old Backups


By:   |   Last Updated: 2015-02-09   |   Comments (17)   |   Related Tips: More > Express Edition

Problem

SQL Server Express Edition serves many line-of-business systems as a backend database engine. Being a SQL Server DBA, you should make sure that all your database servers are regularly backed up in an automated way. Unfortunately, SQL Server Express Edition does not include SQL Server Agent in order to schedule the SQL Server database backups. How could we automate the backups for SQL Server Express Edition and make sure the old backup files are deleted?

Solution

We can use PowerShell with Windows Task Scheduler to write an automated backup and cleanup script for the SQL Server Express Edition backups.

It is a simple piece of code that you need to run after specifying the following parameters:

$serverName : The SQL Server name.

$backupDirectory : The path where you will keep the backup file.

$daysToStoreBackups : The number of days to keep the backup file in the backup folder.

The code will check the .BAK files in the backup directory and delete the files older than the ($daysToStoreBackups). Then it will loop through the user databases on your SQL Server and issue backups:

$serverName = "localhost"
$backupDirectory = "D:\backupSQL"
$daysToStoreBackups = 0

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

$dbs = $server.Databases

Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
           $dbName = $database.Name      
        
            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"
 
            $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
            $smoBackup.Action = "Database"
            $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
            $smoBackup.BackupSetName = $dbName + " Backup"
            $smoBackup.Database = $dbName
            $smoBackup.MediaDescription = "Disk"
            $smoBackup.Devices.AddDevice($targetPath, "File")
            $smoBackup.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath"
               
}

In order to schedule this script to be run automatically, you need to save it as a .ps1 file. Then from the Task Scheduler create a task using Create Basic Task as follows:

In order to schedule this script to be run automatically, you need to save it as a .ps1 file.

Going through the basic steps for naming and scheduling that task, we will choose to “Start a program” once we are asked to specify what this task will perform.

In the Edit Action window, we will choose which program we will run in this task. In the program field, specify the path for the PowerShell EXE (C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe).

Then in the argument field specify the path and name for the backup PowerShell script (C:\ScheduledTasks\SQL_Express_Job.ps1) as shown below:

Then in the argument field specify the path and name for the backup PowerShell script (C:\ScheduledTasks\SQL_Express_Job.ps1)

Click finish and now your backup is fully scheduled without a SQL Server Agent job.

Next Steps


Last Updated: 2015-02-09


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

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.



    



Friday, March 23, 2018 - 7:13:24 AM - Eben Back To Top

 Hello Ahmad - This article is impressive and very useful for Express backups, this thread also appears old, but should you have sometime to assist it would do great, while scheduling this PS script via task scheduler with an Windows service account what would be the minimal previliege (on Windows and DB end) required for the job to run , as currently its running when made to run with highest previlieges , also being an DBA having full access on DB and the windows machine for the Task to complete perfect.

Cheers & Wishes

Eben


Sunday, February 25, 2018 - 2:42:44 AM - Ahmad Yaseen Back To Top

 Dear Firdous,

 

Please check that you set the $daysToStoreBackups = 0 parameter correctly and that the SQL user that is used to run the backup job has full accesss on the backup folder.

 

Best Regards,

Ahmad

 


Thursday, February 22, 2018 - 8:03:54 AM - Firdous Back To Top

Thank You Mr.Ahmed Yaseen..

I have set backup $daysToStoreBackups = 1 , but after one day the dabase is not deleting , it is creating another backup database.Waiting for your reply..

 

 

 


Wednesday, February 21, 2018 - 1:53:03 AM - Ahmad Yaseen Back To Top

 Thank you Firdous for your comment.

 

You can remove the foreach part and specify the database name in that variable definitiob:

$dbName = $database.Name    

Best Regards,
Ahmad

 


Tuesday, February 20, 2018 - 9:22:16 AM - Firdous Back To Top

Hi,

 

How can we backup only one database using above script ? 

 

 


Friday, November 18, 2016 - 10:53:09 AM - Greg Robidoux Back To Top

Hi Gaff,

you should be able to edit this line in the code:

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})

to

foreach ($database in $dbs)


Friday, November 18, 2016 - 10:43:55 AM - Gaff Back To Top

This only does back ups of databes ive created. How do yu back up system databaes like master.

 


Tuesday, August 02, 2016 - 1:09:48 PM - Laura Back To Top

How would you alter this script to exclude databases that are offline? 

 


Saturday, June 18, 2016 - 3:36:25 AM - Ahmad Yaseen Back To Top

 Hello Constantin,

Please make sure tha the user running that script has access on the SQL Server and the service account for SQL Server has full access on the folder where you keep the SQL backup files.

 

Best Regards,

Ahmad

 


Thursday, June 16, 2016 - 4:40:50 AM - Constantin Back To Top

I've been trying to run this script but i'm having issues - the system that i'm running it on is windows 2012 R2 and SQLEXPRESS 2014.

the error is

Exception calling "SQLBackup" with "1" argument(s): backup failled for server 'myserver\sqlexpress'. "

At  c:\BackupSQL\Tools\SQL_express_Job.ps1:31 char:13

*                      $smoBackup.SqlBackup($server)

       * category info             : NotSpecified: (:) [], MethodInvocationException

       *FullyQualifiedErrorId   : FailedOperationException

 

Any idea why would i get above ? i'm new to sql and i'm sorry id my question is stupid or an easy fix.


Thanks a million


Sunday, March 06, 2016 - 8:07:57 PM - Hal Back To Top

Simple, easy to implement, and it works. Just wanted to say thanks, this little script is very much appreciated!


Wednesday, January 20, 2016 - 1:19:05 PM - malberto Back To Top

Great article. Thanks for sharing this.


Monday, April 06, 2015 - 2:43:38 PM - Ahmad Yaseen Back To Top

 

HI Scott,

This should work as you can work with powershell in windows server 2003.

Please try it and let me know your feedback.

 

Regards,

Ahmad


Friday, April 03, 2015 - 3:48:27 PM - scott Back To Top

Nice article, I need to do this but with SQL Server 2005  Express but on Windows 2003 Server - would your code still work?

Thanks

 

SC


Friday, February 20, 2015 - 9:35:38 AM - bernie black Back To Top

Nice.  I always make a hardcoded sql script job...which works almost as well as yours BUT won't backup any NEW databases the user may create.  This script will solve that nagging problem.  Thanks.


Friday, February 13, 2015 - 1:07:18 AM - Ahmad Yaseen Back To Top

Thank you Charles for your comment.

What is required is, after you navigate the path of that ps1 file using:

cd  C:\SQL_Express_Backups\Scheduled_Tasks

You need to run any powershell function or script file after putting .\ before it as below:

.\SQL_Express_Job.ps1

 

Please try it and let me know if this fix the issue.

 

Best Regards,

Ahmad

 

 


Thursday, February 12, 2015 - 1:49:26 PM - Charles Milstead Back To Top

OK I will state to begin with that I am no DBA but feel comfortable enough with the stuff to be able to make it work as the Beatles say, "With a little help from my friends".  I received the error below and have been trying to figure out why Powershell can't recognize the sript as a proper cmdlet?  Any ideas?  

PS SQLSERVER:\SQL\RECEPTION2\SQLEXPRESS+sa\BackupDevices\> C:\SQL_Express_Backup

s\Scheduled_Tasks\SQL_Express_Job.ps1

C:\SQL_Express_Backups\Scheduled_Tasks\SQL_Express_Job.ps1 : The term 'C:\SQL_E

xpress_Backups\Scheduled_Tasks\SQL_Express_Job.ps1' is not recognized as the na

me of a cmdlet, function, script file, or operable program. Check the spelling

of the name, or if a path was included, verify that the path is correct and try

 again.

At line:1 char:59

+ C:\SQL_Express_Backups\Scheduled_Tasks\SQL_Express_Job.ps1 <<<<

    + CategoryInfo          : ObjectNotFound: (C:\SQL_Express_...Express_Job.p

   s1:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

 

PS SQLSERVER:\SQL\RECEPTION2\SQLEXPRESS+sa\BackupDevices\>


Learn more about SQL Server tools