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
@vCountInt,
@vNumDBsInt,
@vDBNameVarchar(255),
@vBackupPathVarchar(255),
@vFileNameVarchar(100),
@vCreateStringVarchar(1000),
@vBackupStringVarchar(1000),
@vRestoreStringVarchar(1000),
@vDeleteStringVarchar(1000),
@vNewPathVarchar(1000),
@vOldFilesVarchar(1000),
@vErrorStringVarchar(1000)


Set @vBackupPath = 'F:\Backup'

Declare
@vDBListTable(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseNameVarchar(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