There are plenty of programs you can purchase that will allow you to schedule backups for SQLExpress 2008. Only issue with those is that they often set you back $50 - $100. However, there are FREE alternatives...
If you only have 1 or 2 DBs to backup and don't mind backing them up daily then there is a free program called SQL backup and FTP. As I mentioned above, there are limitations with this. It only allows a maximum of 2 DBs to be backed up and you don't have any scheduling flexibility, it's daily or it's nothing. Those factors made this unsuitable for my needs.
Enter the 2nd FREE option...Automating a T-SQL script with windows task scheduler.
This is a simple 5 step process to scheduled backups whenever you want.
- Run the T-SQL stored procedure against your DB
- Create a folder to store the scripts on the computer running the database. (i.e. BackupScripts)
- Create a file called "db_backup.sql" in the "BackupScripts" folder containing the following sql:
exec expressmaint @database = '$(DB)', @optype = 'DB', @backupfldr = '$(BACKUPFOLDER)', @reportfldr = 'C:\DB_Backups\Reports', @verify = 1, @dbretainunit = '$(DBRETAINUNIT)', @dbretainval = '$(DBRETAINVAL)', @rptretainunit = 'copies', @rptretainval = 2, @report = 1
```
Where 'C:\DB_Backups\Reports' is equal to the folder you want the report stored in
- The fourth step is to create another file called "db_backup.bat" that will be scheduled to run the script file above.
sqlcmd -S .\SQLExpress -i"C:\Scripts\db_backup.sql" -v DB="Blog" -v BACKUPFOLDER="C:\DB_Backups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"
```
Where "C:\Scripts\db_backup.sql" is the located of your script file and "Blog" is the db name, and "C:\DB_Backups" is where the backups will be stored.
- The fifth and final step is to simply setup a scheduled task for the "db_backup.bat" file
The great thing about this option is that it's free and you can schedule it to run when ever you want unlike the free program. I tested this using SQLExpress 2008 but this should also work on SQLExpress 2005.
