posted on Sunday, August 13, 2006 5:10 PM by bknight

Automating SQL Express Backups

SQL Express allows you to distribute a free edition of SQL Server with your applications. There are an enormous amount of 3rd party applications that use it and MSDE (it's SQL Server 2000 equivalent) for their data repository such as Sharepoint (by default). Generally speaking, the user may not even know they have the software on their workstation since it doesn't install anything on their Programs menu. The main weakness in SQL Express is that it doesn't have a scheduling tool. In this set of scripts, you'll receive a canned solution for automating backups of SQL Express. The same logic can be applied to MSDE by changing SQLCMD to OSQL in the batch file. No other syntax used is SQL Server 2005 specific.

The script contains three files:

  • BackupExpress.sql - Does the bulk of the work, backing up every database on the instance other than tempdb.
  • BackupExpress.cmd - The batch file that executes the script. Must pass in the instance name.
  • ScheduleBackups.cmd - Schedules the job by using AT. Must pass in the instance name to schedule like ScheduleBackups.cmd .\SQLExpress.

To do this, make sure you have the Task Scheduler service started. Uncompress all three of the files into the root of your C drive and run the schedule file from the command prompt.

I tried to keep the solution simple since most people that have SQL Express on their workstation may not be technical. So with that said, there is very little configuration or options. The backup solution keeps 7 days of history and constantly overwrites the previous week's backup. So, there's lots of room for improvement if you're feeling adventurous. Please send me any updates and I'll post them for all, giving you credit. The main thing missing from this solution is the index rebuilds, which I'll probably add in the next release.

Download the package here: http://www.whiteknighttechnology.com/downloads/BackupExpress.zip. Enjoy!

Watch the deployment video on JumpstartTV here: http://www.jumpstarttv.com/Media.aspx?vid=30.

-- Brian Knight

Comments

# SQL Express Backups

Sunday, August 13, 2006 6:31 PM by .NET
I have been looking for something like this for a long time:
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx...

# Automating SQL Express Backups

Monday, August 14, 2006 6:10 AM by Andrew Connell [MVP MCMS]

# re: Automating SQL Express Backups

Monday, August 14, 2006 7:18 AM by rav
Sweet .. thank you!
Can't wait to see more simple examples using SSE. :)

rav

# re: Automating SQL Express Backups

Wednesday, November 15, 2006 9:27 AM by Garyp
Nice and simple and it works on SQL 2005 Express and SQL 2000. What would it take to make it backup the Transaction Logs also? I tried to watch the video @ jumpstarttv, but all it played was the commercial intro. Keep up the good work.

# re: Automating SQL Express Backups

Wednesday, April 18, 2007 7:51 AM by Dan
Great tool, looked for something just like that.
One question - is there a work around for DB with spaces in names? So instead of AdventureWorks name it would be Adventure Works.
Thanks.

# re: Automating SQL Express Backups

Friday, November 16, 2007 9:07 AM by smitch
Hi Brian, best script i've seen so far, but I can't seem to get it to run automatically. I ran the backupexpress.cmd to set the scheduled task,the task said it ran, but no backups in the folder. I am missing something. It will not start the query by itself. How do you do that? Thanks

# re: Automating SQL Express Backups

Wednesday, February 06, 2008 11:26 PM by Doug
I ran the backup, however I keep getting an error when running it. the error is:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Any help would be greatly appreciated.
Thanks,
Doug

# Automating SQL Express Backups

Tuesday, March 04, 2008 6:13 AM by i386
Automating SQL Express Backups

# Automating SQL Express Backups

Tuesday, March 04, 2008 6:15 AM by i386
Automating SQL Express Backups

# Automating SQL Express Backups

Tuesday, March 04, 2008 6:15 AM by i386
Automating SQL Express Backups

# Automating SQL Express Backups

Wednesday, March 12, 2008 11:24 AM by i386
Automating SQL Express Backups

# re: Automating SQL Express Backups

Friday, March 14, 2008 7:59 AM by Richnep
I had change the -s variable from 1% to the actual server name and it worked great.

# re: Automating SQL Express Backups

Tuesday, March 18, 2008 7:18 AM by zxevil163
6lt1O6 Hi from Russia!

# re: Automating SQL Express Backups

Thursday, March 20, 2008 6:03 AM by zxevil164
EMiIGL Cool, bro!

# re: Automating SQL Express Backups

Wednesday, April 09, 2008 2:46 AM by bheyer
I get the same error as Doug does. Within SQL Server Management Studio Express it works well.

Any one with a solution for this error?

Grtz

# re: Automating SQL Express Backups

Monday, May 12, 2008 3:51 PM by cjidnndsve

# re: Automating SQL Express Backups

Tuesday, May 20, 2008 8:34 PM by Jon_ProactiveLogic
Great scripts, it helped me quite a bit. Thank you!!!

# re: Automating SQL Express Backups

Saturday, August 02, 2008 11:46 AM by gtijvszxzsp

# re: Automating SQL Express Backups

Thursday, October 16, 2008 7:48 AM by Dustin Jones
This is just what I was looking for! Easy, simple, and gets the job done. Thanks Brian.

# re: Automating SQL Express Backups

Thursday, October 16, 2008 7:48 AM by Dustin Jones
This is just what I was looking for! Easy, simple, and gets the job done. Thanks Brian.