This stored procedure was written to get around the lack of support for maintenance plans in MSDE (and to bypass using the naff SSIS-based maintenance plans in SQL 2005). The user specifies the type of backup (FULL, LOG or DIFFERENTIAL), the db name, the folder to hold the backup file and the retention period in days (anything older in the backup folder for the specified backup type is deleted). See usage instructions in the code or run the procedure with no parameters.
The stored procedure also has a parameter for debugging. Set to 1 to have commands printed out during the execution of the procedure.
23-Sep-2008 Update: I have been making updates to this script and posting them in the discussion forum but I have figured out how to update the original post. So I have.
This version has improved long filename handling (i.e. it does now), options to backup all system, all user or all databases, support for file and filegroup backups (full and differential) and better checking of SQL Server version (some things work in 2005 but not in 2000).
It is assumed that the version of SQL Server being used is 2005 SP2 or later or 2000 SP4 or later.