Script for Full Backup - removes old backups

/*A proc which gets a list of all databases (Exluding "excluded" ones) and backs them up.
The backup files are stored in the directory \DatabaseName\DatabaseName.bak
UNC names are supported provided SQL Server has write access to the location.

It deletes backups older than x (Current 2) days.This can be changed.

Add a job to execute the script of proc on a daily basis e.g. "exec master.dbo.SP_GlobalFullDBBackup"
Backups performed are Full.

Last Modified by P.Muiruri on 12th April 2005
 */

CREATE PROCEDURE SP_GlobalFullDBBackup
as

Set NoCount On
Set quoted_identifier off

Declare
	@vCount		Int,
	@vNumDBs	Int,
	@vDBName	Varchar(255),
	@vBackupPath	Varchar(255),
	@vFileName	Varchar(100),
	@vCreateString	Varchar(1000),
	@vBackupString	Varchar(1000),
	@vRestoreString	Varchar(1000),
	@vDeleteString	Varchar(1000),
	@vNewPath	Varchar(1000),
	@vOldFiles	Varchar(1000),
	@vErrorString	Varchar(1000)


Set @vBackupPath = 'F:\Backup'

Declare
	@vDBList	Table(
				DBID INT NOT NULL IDENTITY(1, 1),
				DatabaseName	Varchar(256)
				)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases Where Name NOT IN ('Master','msdb','model','Northwind','pubs')
Set @vNumDBs = @@RowCount
Set @vCount = 1

While @vCount < @vNumDBs
	Begin
		Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
		Set @vNewpath = @vBackupPath + '\' + @vDBName + '\'
		/*--Create criteria for deleting old files - older than 2 days */
		Set @vOldFiles = @vBackupPath + '\' + @vDBName + '\'+ @vDBName+'-'+
		LEFT(CONVERT(VARCHAR(19), getdate(), 120),8)+
			SUBSTRING(CONVERT(VARCHAR(19), DateAdd(Day, -2, getdate()), 120),9,2)++'??????'+'Full'+'.bak'
		--Create Delete String
		Set @vDeleteString = 'Del'+' '+ @vOldFiles + ' /F /Q'
		--Excute delete string to delete backup files older than 2 days
		Exec xp_CMDShell @vDeleteString  --, NO_OUTPUT
		--Create Directory Structure if not present
		Set @vCreateString = 'MD ' + @vNewPath
		Exec xp_CMDShell @vCreateString, NO_OUTPUT
		--Generate backup file name from getdate() function converted to string type varchar()
		Set @vFileName =  @vDBName + '-'+
		LEFT(CONVERT(VARCHAR(19), getdate(), 120),10)+'-'
		+SUBSTRING(CONVERT(VARCHAR(19), getdate(), 120),12,2)+'-'
		+SUBSTRING(CONVERT(VARCHAR(19), getdate(), 120),15,2)+'Full'+ '.bak'	
		--Generate backup command name from getdate() function converted to string type varchar()
		Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + @vFileName 
			+ ''' WITH  INIT ,  NOUNLOAD , NAME = N''' + 
			@vDBName + ''',  NOSKIP ,  STATS = 50, NOFORMAT'
		/*Test restore. Will not tell you if the backup CAN be restored successfully!
		--Generate restore file name from as above
		Set @vRestoreString = 'RESTORE VERIFYONLY ['+@vDBName + '] 
			FROM DISK = '''+RTRIM(@vBackupPath+'\'+@vDBName+'\'+@vFileName)
		*/
		Exec (@vBackupString)
		/* Verify not working. Run time error. Work in progress.
		Print @vRestoreString
		Exec (@vRestoreString
		*/
		If @@Error <> 0
			Begin
				Set @vCount = @vNumDBs
				Set @vErrorString = 'net send k1hq1oa0494 SQL Backup failed on (' + @vDBName + ')
				! Please view event log!'
				Exec XP_CMDShell @vErrorString
			End
		Set @vCount = @vCount + 1
	End
GO