SQL 2005 Express DB Backups on Windows Server 2003 SBE

Question

Is there a way to schedule a job on Windows Server 2003 [small business edition] that will create a backup of a SQL 2005 Express DB?

I'm not sure if there is a native job/process for this on SQL Express or not. If there is not, how would I go about creating one?

Answer 1

I use expressmaint - works like a champ

Answer 2

I believe Quest Software's freeware version of Toad has the ability to manage SQL jobs on SQL Server express.

Another option would be to script out the task using sqlcmd and schedule it using the Windows task manager.

Answer 3

I was using expressmaint for a while then when I upgraded to SQL Express 2008 I had to switch to SalBackupAndFTP. It lets you schedule backups, etc, and supports SQL Express 2005/08, oh and its free. (BTW the ftp part is optional, it also lets you save to disk)

Answer 4

To elaborate on the second part of boxelephant's answer, here's a sample script you could save as a bat file and run via Windows scheduler.

sqlcmd -E -S .\sqlexpress -Q "BACKUP DATABASE [MyDatabaseName] TO DISK = N'c:\Backups\MyDatabaseName.bak' WITH NOFORMAT, NOINIT, NAME = N'My Database-Full Database Backup' , SKIP, NOREWIND, NOUNLOAD, STATS = 10" 

This just logs into the local sqlexpress instance using a trusted connection and creates a full backup of MyDatabaseName and saves it to disk. You'd probably want to tweak the script to incorporate some sort of date/time stamp in the bak file name so you don't overwrite the previous backup.

The sqlcmd syntax can be found here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

I've never used either of the third-party solutions mentioned, but if one meets your needs, it's likely easier to use one of them than rolling your own from scratch. OTOH, this is still pretty easy if all you need is a super lightweight backup plan.

Answer 5

Microsoft SQL Server Management Studio Express allows for backups, among all the other things it can do.