--works with 2000/2005/2008
DECLARE @retvalue int, @data_dir varchar(255)
DECLARE @MyBackupName nvarchar(255)

EXECUTE @retvalue = xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
 N'BackupDirectory', @param = @data_dir OUTPUT
PRINT 'SQL Server Backup Path: '+ @data_dir 
SET @MyBackupName = @data_dir + '\events2hvac_' + convert( varchar(10), getdate(), 112 ) + '.BAK'
PRINT 'Backup filename: '+ @MyBackupName 

IF EXISTS (SELECT name FROM master..sysdatabases WHERE name = N'Events2HVAC')
BEGIN
--backup to disk, don't append to file.
EXECUTE('BACKUP DATABASE [Events2HVAC] TO  DISK = ''' + @MyBackupName +''' WITH NOFORMAT, INIT,  NAME = ''E2H-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10')
END
GO

