Automating SQL Express Backups

We recently had a project go live which utilizes a SQL Express database.  Somehow we let the project go live without a true backup story for the database.  SQL Express doesn’t include all the features of SQL Server, which means we were left to roll our own backup strategy.  After searching on the net, I found this article posted on SQLDBATIPS.

The procedure in the article allows a great way to get database backups automated with a lot of flexibility in how you want to do it.  I chose to perform a backup every day and store the backups for 1 week.

After adding the stored procedure, I was faced with errors when I ran it.  These errors are a result of SQL Express being locked down by default.  It doesn’t mention in the article how to resolve these errors, so I searched out and found the answers.  The two types of errors I received were Ole Automation wasn’t enabled and xp_cmdshell wasn’t enabled.  Here is the script I ran to fix it: 

– Run this to enable Ole Automation Procedures if needed.
sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
sp_configure ’show advanced options’, 0
GO

– Run this to enable xp_cmdshell if needed.
sp_configure ’show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ’show advanced options’, 0
GO
RECONFIGURE;
GO