Backup and Schedule SQLExpress 2008 Database for FREE

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.

1. Run the T-SQL stored procedure against your DB
2. Create a folder to store the scripts on the computer running the database. (i.e. BackupScripts)
3. 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

4. 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.

5. 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.