This script is missing some features:

Deleting of old backups

Excluding off line db's

Excluding stand by db's

Below the backup scripts for full and transaction backups I use for some years (If I remember correctly these scripts are based on scripts originally made by Tara Kizer).

Daan Stam

Database Administrator

AWVN

The Netherlands

/****** Object: StoredProcedure [dbo].[mySP_BackupFullAllDatabases] Script Date: 05/22/2008 09:55:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Daan Stam

-- Create date: 06-06-2007

-- Description:Backup of databases to individual subdirs

-- Deleting old backups

-- =============================================

CREATE PROCEDURE [dbo].[mySP_BackupFullAllDatabases]

@myBackupDir varchar(255),

@myDaystoKeepFullBackups int,

@myDaystoKeepTransBackups int,

@myRetMsg nvarchar(4000) =Null OUTPUT

AS

BEGIN

--SET NOCOUNT ON;

/** cursor for databases to backup **/

Declare myCursor cursor for SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('tempdb') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' and [filename] not like '%.ss' order by name

/** variable for loop through databases **/

declare @mydb as varchar(255)

/**variable for backup name **/

declare @myDirName as varchar(255)

declare @myBackupName as varchar(400)

/** Clean up backup directory **/

declare @DeleteDateStr nvarchar(30)

set @DeleteDateStr=Convert(nvarchar(30),getdate()-@myDaystoKeepFullBackups ,1)+' '+Convert(nvarchar(30),getdate()-@myDaystoKeepFullBackups ,8)

EXECUTE master.sys.xp_delete_file 0,@myBackupDir,'bak',@DeleteDateStr,1

set @DeleteDateStr=Convert(nvarchar(30),getdate()-@myDaystoKeepTransBackups ,1)+' '+Convert(nvarchar(30),getdate()-@myDaystoKeepTransBackups ,8)

EXECUTE master.sys.xp_delete_file 0,@myBackupDir,'trn',@DeleteDateStr,1

/**backup all databases**/

set @myRetMsg='Start'

open myCursor

fetch next from myCursor into @mydb

while (@@fetch_status<>-1)

begin

/** create backup dir **/

if len(replace(@mydb,' ',''))>0

begin

begin try

print ' '

print '--' + @mydb

/**create backupdir **/

set @myRetMsg= @myRetMsg + char(13) + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Start ' + @mydb

set @myDirName =@myBackupDir + '\'+ @mydb

EXECUTE master.dbo.xp_create_subdir @myDirName

/**backup database **/

set @myBackupName= @mydb + '_backup' + convert(varchar(20),getdate(), 112) + replace(convert(varchar(20),getdate(), 108),':','') +'.bak'

set @myDirName=@myDirName +'\'+ @myBackupName

BACKUP DATABASE @mydb TO DISK = @myDirName WITH NOFORMAT, NOINIT, NAME = @myBackupName, SKIP, REWIND, NOUNLOAD, STATS = 10

set @myRetMsg= @myRetMsg + char(13)+ convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' End ' + @mydb

end try

begin catch

set @myRetMsg= @myRetMsg + char(13)+ convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Error in backup of database ' + @mydb

set @myRetMsg= @myRetMsg + char(13) + @mydb + ' Error Occurred'

set @myRetMsg= @myRetMsg + char(13) + cast(ERROR_NUMBER()as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_SEVERITY() as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_STATE() as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_MESSAGE() as varchar(60))

end catch

/** Verify backup **/

begin try

print ' '

print '--' + @mydb

set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' Start verify ' + @mydb

RESTORE VERIFYONLY FROM DISK = @myDirName

set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) + ' End verify ' + @mydb

end try

begin catch

set @myRetMsg= @myRetMsg + char(13) + convert(varchar(20),getdate(), 105)+ ' ' + convert(varchar(20),getdate(), 108) +' Error in verify backup' + @mydb

set @myRetMsg= @myRetMsg + char(13) + @mydb + ' Error Occurred'

set @myRetMsg= @myRetMsg + char(13) + cast(ERROR_NUMBER()as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_SEVERITY() as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_STATE() as varchar(60))

set @myRetMsg= @myRetMsg + char(13) + cast( ERROR_MESSAGE() as varchar(60))

end catch

end

/** next database **/

fetch next from myCursor into @mydb

-- Let the system rest for 5 seconds before starting on the next backup

waitfor delay '00:00:05'

end

deallocate myCursor

END

/****** Object: StoredProcedure [dbo].[mySP_BackupTransAllDatabases] Script Date: 05/22/2008 10:00:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Daan Stam

-- Create date: 06-06-2007

-- Description:Backup Transactions of databases to individual subdirs

--

-- =============================================

CREATE PROCEDURE [dbo].[mySP_BackupTransAllDatabases]

@myBackupDir varchar(255),

@myRetMsg varchar(400) =Null OUTPUT

AS

BEGIN

--SET NOCOUNT ON;

/** cursor for databases to backup **/

Declare myCursor cursor for select [name] from master..sysdatabases where [name]<>'' and [name]<>'model' and [name]<>'tempdb' AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' and DATABASEPROPERTYEX([name], 'RECOVERY') = 'FULL' and [filename] not like '%.ss' order by [name]

/** variable for loop through databases **/

declare @mydb as varchar(255)

/**variable for backup name **/

declare @myDirName as varchar(255)

declare @myBackupName as varchar(400)

/**backup transactions all databases**/

open myCursor

fetch next from myCursor into @mydb

while (@@fetch_status<>-1)

begin

/** create backup dir **/

set @myDirName =@myBackupDir + '\'+ @mydb

EXECUTE master.dbo.xp_create_subdir @myDirName

/**backup database **/

set @myBackupName= @mydb + '_backup' + convert(varchar(20),getdate(), 112) + replace(convert(varchar(20),getdate(), 108),':','') +'.trn'

set @myDirName=@myDirName + '\' + @myBackupName

BACKUP LOG @mydb TO DISK = @myDirName WITH NOFORMAT, NOINIT, NAME = @myBackupName, SKIP, REWIND, NOUNLOAD, STATS = 10

fetch next from myCursor into @mydb

-- Let the system rest for 5 seconds before starting on the next backup

waitfor delay '00:00:05'

end

deallocate myCursor

set @myRetMsg=@@rowcount

return 0

END