SQL Script for date coded backups of Events2HVAC database

Here's a sample backup script that you can use to backup your Events2HVAC database using SMSEE or with an automated task in windows Task Scheduler.

When this is executed, it will determine the default SQL Server backup directory and create a backup file in the format:


where yyyy = year, mm=month, dd=day

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

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request


Article is closed for comments.