How to overcome lack of SQL Agent Job Scheduling Service on SQL Express Edition?
772
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?
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.
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).
You can use external backup software like Cobian Backup for scheduling and compressing.
For the database backup itself, one option is to use ExpressMaint
I've worked around this in two ways:
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):
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.
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).
See here:
SQL Server 2008 Express - "Best" backup solution?
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:
https://stackoverflow.com/questions/487675/how-can-i-schedule-a-daily-backup-with-sql-server-express