use master

create procedure sp_easy_backup
@backup_directory varchar(100) = 'c:\SQL Express Backups'        -- default backup folder if none passed

set nocount on
declare @weekday varchar(10)
declare @todays_directory varchar(120)
declare @cmd varchar(200)
declare @db_name varchar(100)
declare @recovery_model_desc varchar(20)

if @backup_directory is null        -- in case null is explicitly passed
       select @backup_directory = 'c:\SQL Express Backups'

if substring(reverse(@backup_directory), 1, 1) <> '\'
       select @backup_directory = @backup_directory + '\'

if patindex(@backup_directory, @@servername) = 0
       select @backup_directory = @backup_directory + @@servername + '\'

-- make daily directory if needed

select @weekday = case (7 - @@datefirst + datepart(dw, getdate()))
       when 1 then 'Sunday'
       when 2 then 'Monday'
       when 3 then 'Tuesday'
       when 4 then 'Wednesday'
       when 5 then 'Thursday'
   when 6 then 'Friday'
       when 7 then 'Saturday'
       else 'Unknown'

select @todays_directory = @backup_directory + @weekday + '\'

create table #existence
file_exists int,
is_dir int,
parent_exists int

insert #existence EXEC master.dbo.xp_fileexist @todays_directory

if not exists (select * from #existence where is_dir = 1)
       select @cmd = 'mkdir "' + @todays_directory + '"'
       exec master.dbo.xp_cmdshell @cmd

declare db_cursor insensitive cursor for
       select name, recovery_model_desc from sys.databases where state_desc = 'ONLINE' and name <> 'tempdb'
               for read only
open db_cursor

fetch db_cursor into @db_name, @recovery_model_desc

while @@fetch_status >= 0

       select @cmd = 'backup database ' + @db_name + ' to disk = ''' + @todays_directory + @db_name + '.bak'' with
       exec (@cmd)

       if @recovery_model_desc <> 'SIMPLE'
               select @cmd = 'backup log ' + @db_name + ' to disk = ''' + @todays_directory + @db_name + '.bak'' with
               exec (@cmd)

       fetch db_cursor into @db_name, @recovery_model_desc

close db_cursor
deallocate db_cursor
drop table #existence

end                -- procedure easy_backup

grant execute on sp_easy_backup to public
sp_easy_backup.sql - procedure to backup all databases, for use with SQL 2005 Express

              sp_easy_backup [optional_path]

Author:              Fred Williams

Description:     Since Express Edition does not include SQL Agent, here is an easy way to implement a
daily backup of all databases. Backups are put in folders named Monday, Tuesday, etc., and are
overwritten after 7 days.

Directions:       Create this procedure in the m
aster database (just highlight and copy to the clipboard...)

Then create a daily Scheduled Task to run sqlcmd with this procedure called, as:

C:\> echo sqlcmd -E - Q "sp_easy_backup" >c:\BackupDBs.bat

C:\> schtasks /create /tn "Backup Databases" /tr c:\BackupDBs.bat /sc daily /st 05:00:00 /ru "System"