This article will show you how to create an automated SQL backup task to backup your Events2HVAC database. For this to work, you must be using SQL 2005/2008 Express on the local machine where the Events2HVAC server is located. If your database is hosted on the full version of SQL Server, then you can create an automated maintenance backup plan using SQL Management Studio.
Start by creating your backup SQL script in Notepad and name it 'E2HBackup.sql'. A starter file can be found as an attachment to this document. Below is a sample for a full database backup to the ..\backup directory located in the default Events2HVAC application directory location. You should test this script using SQL Management Studio to verify it works first.
BACKUP DATABASE [Events2HVAC] TO DISK = N'<<enter your full path here>>\Events2HVAC.bak' WITH NOFORMAT, INIT, NAME = N'E2H-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Note for Windows Server 2008 and Windows 7: The path to which you will write your backup file must have the appropriate full access permissions for SQL server to write to it. To avoid this issue, use the default SQL Server backup path.
Store the E2HBackup.sql file in this location:
C:\Program Files\Streamside Solutions\Events2HVAC\Scripts\E2HBackup.sql
Open the Schedule New Task wizard. This is shown below (in Windows XP):
Start Menu > All Programs > Accessories > System Tools > Scheduled Tasks > Add Scheduled Task
Point to your local SQL server BINN directory and select "SQLCMD.exe".
SQL 2008 path:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe
SQL 2005 path:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.exe
Give the task a name and select the frequency of execution.
Enter your specific database backup schedule.
Enter a set of Windows user credentials with sufficient rights to make a full database backup in SQL.
Click on the checkbox to open advanced properties.
Add the rest of the backup command parameters to the Run field. The Run field should contain the following items:
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -i "C:\Program Files\Streamside Solutions\Events2HVAC\Scripts\E2HBackup.sql"
where the parameter .\SQLEXPRESS is the SQL instance on the local machine. If you changed the default instance, you will need to adjust this parameter appropriately. This value should match what you have entered for the SQL Server Name field in the Database tab in Tools > Events2HVAC Options .
Note: If you are using SQL Express 2005 instead of 2008, the path to the SQLCMD.exe file will be:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE"
Verify the new task can run successfully:
After completion of the task, you should find a backup file named Events2HVAC.bak located in the path you chose in your script file:
<<enter your full path here>>\Events2HVAC.bak