Binaryworld - Database Backup Script ... [ SQL Server -> Backup-Restore ]

The procedure should be placed in an Admin database and scheduled - normally to run every day. The table DatabaseBackup should be created as defined near the top of the procedure. The call to be scheduled is in comments near the top of the procedure.
It will backup every database on the server to the path defined except tempdb. The database entries will be placed in the DatabaseBackup table and may be configured from there. The table fields are:

Field Name Description
DatabaseBackupName Name of database - added automatically if it doesn't exist
BackupFlagFull 'Y' = Full backup of database, 'N' = No full backup
BackupFlagLog 'Y' = Log backup of database, 'N' = No log backup
RetentionPeriodFull Datetime period for which to keep the full backup
RetentionPeriodLog Datetime period for which to keep the log backup

Note - if an exact number of days is set for retention period and the procedure is scheduled daily then the last retained file will depend on the exact time of processing the backup of that database. The files are created with the following format:

[dbname]_Full_yyyymmdd_hhmmss.bak
[dbname]_Log_yyyymmdd_hhmmss.bak

Click here to copy the following block

if exists (select * from sysobjects where id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBackupAllDatabases]
go

Create Procedure spBackupAllDatabases
@Path varchar(128) ,
@Type varchar(4) -- Full / Log
as
/*
Backup file format
<dbname>_Full_yyyymmdd_hhmmss.bak
<dbname>_Log_yyyymmdd_hhmmss.bak

exec spBackupAllDatabases 'c:\SQLBackups\', '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' ,
'N' ,
'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) <> 'tempdb'

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

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

-- Delete old backups
delete #ExistingBackups
select   @sql = 'dir /B ' + @Path
select @sql = @sql + @Name + '_' + @Type + '*.*'

insert #ExistingBackups exec master..xp_cmdshell @sql

if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups

select   @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep

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

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
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
else
backup log @Name
to disk = @sql
end

go