Automate SQL Server Express Backups and Purge Old Backups


By:   |   Updated: 2015-02-09   |   Comments (18)   |   Related: 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





Comments For This Article




Friday, December 27, 2019 - 10:44:04 PM - mau_lopez Back To Top (83542)

Hello Ahmad, I'm not a DBA but found this post and it's exactly what I need right now. I'm having the same problem as Constantin below. I've tried to ensure that "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" but with no joy. I'd really appreciate if you could guide me with more details as how to configure both access things in SQL and Windows. Thanks a lot in advance!


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

 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 (75292)

 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 (75276)

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 (75257)

 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 (75252)

Hi,

 

How can we backup only one database using above script ? 

 

 


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

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 (43804)

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 (43031)

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 (41715)

 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 (41706)

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 (40873)

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 (40455)

Great article. Thanks for sharing this.


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

 

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 (36821)

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 (36300)

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 (36222)

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 (36217)

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\>



download


Recommended Reading

Installing SQL Server 2017 Express

Deciding to use SQL Server 2017 Express Edition

How To Schedule SQL Scripts On SQL Server Express Edition

Getting Started with SQL Server 2017 Express LocalDB

Getting Started with SQL Server 2012 Express LocalDB





get free sql tips
agree to terms


Learn more about SQL Server tools