sp_backupalldatabases

if  exists (select * from sys.objects where object_id = object_id(N'databasebackup') and type in (N'u'))
drop table databasebackup

create table databasebackup
    (
    name                          varchar(128) primary key nonclustered ,
    backupflagfull          varchar(1) not null check (backupflagfull in ('y','n')) ,
    backupflaglog          varchar(1) not null check (backupflaglog in ('y','n')) ,
    retentionperiodfull    datetime not null ,
    retentionperiodlog    datetime not null
    )
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.sp_backupalldatabases') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
  drop procedure dbo.sp_backupalldatabases
end
go
create procedure [dbo].[sp_backupalldatabases]
@path    varchar(128) ,
@type    varchar(4)    -- full / log
as
/*
backup file format
<dbname>_full_yyyymmdd_hhmmss.bak
<dbname>_log_yyyymmdd_hhmmss.bak

exec dbo.sp_BackupAllDatabases 'H:\Backup\', 'Full'

*/
/*
drop table databasebackup

create table databasebackup
    (
    name                        varchar(128) primary key nonclustered ,
    backupflagfull        varchar(1) not null check (backupflagfull in ('y','n')) ,
    backupflaglog        varchar(1) not null check (backupflaglog in ('y','n')) ,
    retentionperiodfull    datetime not null ,
    retentionperiodlog    datetime not null
    )
*/
set nocount on
declare    @sql varchar(1000)
   
    -- get all database names
    create table #dbname
        (
        id        int identity (1,1) ,
        name        varchar(128) not null ,
        retentionperiod    datetime null
        )
       
    insert    #dbname
        (name)
    select    name
    from    master..sysdatabases
   
    -- include any new databases in the backup
    insert    databasebackup
        (
        name ,
        backupflagfull ,
        backupflaglog ,
        retentionperiodfull ,
        retentionperiodlog
        )
    select    #dbname.name ,
        'y' ,
        'y' ,
        '7 jan 1900' ,        -- default one week
        '1 jan 1900'
    from    #dbname
        left outer join databasebackup
            on databasebackup.name = #dbname.name
    where    databasebackup.name is null
    and    lower(#dbname.name) = 'ROGUE'
    --and    lower(#dbname.name) <> 'master'
    --and    lower(#dbname.name) <> 'master'
    --and    lower(#dbname.name) <> 'tempdb'
    --and    lower(#dbname.name) <> 'model'
   
    -- remove any non-existant databases
    delete    databasebackup
    where not exists
        (
        select    *
        from    #dbname
        where    #dbname.name = databasebackup.name
        )
   
    delete    #dbname
   
    create table #existingbackups
        (
        name varchar(128) ,
        id int identity (1,1)
        )
   
    -- loop through databases
declare    @name             varchar(128) ,
    @retentionperiod    datetime ,
    @lastbackuptokeep    varchar(8) ,
    @id            int ,
    @maxid        int   
    --
    insert    #dbname
        (name, retentionperiod)
    select    name, case when @type = 'full' then retentionperiodfull else retentionperiodlog end
    from    databasebackup
    where    ((@type = 'full' and backupflagfull = 'y')
    or    (@type = 'log' and backupflaglog = 'y'))
    --and name = 'ROGUE'
   
    select    @maxid     = max(id) ,
        @id    = 0
    from    #dbname
   
    while @id < @maxid
    begin
        -- get next database to backup
        select    @id = min(id) from #dbname where id > @id
       
        select     @name = name ,
            @retentionperiod = retentionperiod
        from     #dbname
        where    id = @id
       
        select     *
        from     #dbname
       
        -- delete old backups
        delete    #existingbackups
        select     @sql = 'dir /b ' + @path
        select    @sql = @sql + @name + '_' + @type + '*.*'
        print ' delete old backups @sql:' + isnull(@sql,'')
        insert    #existingbackups exec master..xp_cmdshell @sql
       
        select * from #existingbackups
       
        if exists (select * from #existingbackups where name like '%file not found%')
            delete #existingbackups
       
       
           
        --select     @lastbackuptokeep = convert(varchar(12),getdate(),103) - convert(varchar(12),@retentionperiod,103)
        select     @lastbackuptokeep = convert(varchar(8),getdate()-1 ,112)
        select     @lastbackuptokeep
        --delete    #existingbackups where name > @name + '_' + @type + '_' + @lastbackuptokeep
       
            select * from #existingbackups

declare    @eid    int ,
    @emaxid    int ,
    @ename    varchar(128)
       
        -- loop round all the out of date backups
        select    @eid = 0 ,
            @emaxid = coalesce(max(id), 0)
        from    #existingbackups
       
       
                select * from #existingbackups
               
        while @eid < @emaxid
        begin
            select     @eid = min(id) from #existingbackups where id > @eid
            select    @ename = name from #existingbackups where id = @eid
           
            select @sql = 'del ' + @path + @ename
            print 'loop round all the out of date backups @sql:' + isnull(@sql,'')
            exec master..xp_cmdshell @sql
        end
        delete    #existingbackups
   
        -- now do the backup
        select @sql = @path + @name + '_' + @type + '_'
                    + convert(varchar(8),getdate(),112) + '_'
                    + replace(convert(varchar(8),getdate(),108),':','') + '.bak'
        if @type = 'full'
            backup database @name
            to disk = @sql
            with compression
        else
            backup log @name
            to disk = @sql
    end