IS Department


Automating SQL Express Backups

Posted in Network,Programming by Brian Russell on December 19, 2006

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: