SqlSavior.com

use master
go

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

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'
       end

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)
       begin
       select @cmd = 'mkdir "' + @todays_directory + '"'
       exec master.dbo.xp_cmdshell @cmd
       end

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
       begin

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

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

       fetch db_cursor into @db_name, @recovery_model_desc
       end

close db_cursor
deallocate db_cursor
drop table #existence

end                -- procedure easy_backup
go

grant execute on sp_easy_backup to public
go

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

usage:               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 master 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"