Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We want to schedule sql database backups like you are able to do with SQL Agent Job Scheduling Service. Is there any way of doing this backups on the SQL 2005 Express edition?

share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

I've worked around this in two ways:

  • You can use Windows' Scheduled Tasks to execute a script using SQLCMD.
  • If you have other SQL Servers that are not the Express edition, you can create maintenance plans on those servers that point to the Express server.
share|improve this answer
add comment

You can script the backup and use a scheduled task to do it. Here's an example of what to use as the command for your task using the osql command line tool (or what to put in a batch file):

osql -s myserver -E -Q " BACKUP DATABASE MyDB TO DISK = 'C:\SQLBackups\MyDB.bak'"

Here is the MSDN reference page for the SQL 2005 BACKUP statement if you need to incorporate more options to your backup than the very basic statement above.

share|improve this answer
    
Hello squillman. Your command works, but when I restore the file MyDB.bak it will bring all tables, store procedures, security settings, but the login for the sql engine is not mapped. Any idea on how to bring the security settings on the engine itself? –  Geo Jul 28 '09 at 21:51
    
If you're restoring to the same instance that the backup was taken from then the db security should map just fine after a restore. –  squillman Jul 29 '09 at 4:19
add comment

You can use external backup software like Cobian Backup for scheduling and compressing. For the database backup itself, one option is to use ExpressMaint

More answers here:

http://stackoverflow.com/questions/487675/how-can-i-schedule-a-daily-backup-with-sql-server-express

share|improve this answer
add comment

I had this a problem a few years back and have been using the simple batch file and sql script ever since.

i wrote a post covering it here with included scripts and walkthrough: http://www.diaryofaninja.com/blog/2011/02/14/howto-quick-amp-dirty-sql-express-scheduled-backup

basically i do the following: - write a stored procedure that backups up a specified database - write a sql script that calls this for each of the databases i want to backup - call the above script using sqlcmd.exe using a scheduled task

this allows me to choose when it happens on a schedule and run different databases when i want

you can then use a scriptable ftp client like WinSCP (http://winscp.net/) to create another schedule that uploads the backups using FTP or SFTP (much more secure).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.