This article will show you how to create an automated SQL backup task to backup your Events2HVAC database using Windows Server 2008 Task Scheduler. 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.
Here are the steps:
Create your backup SQL script in Notepad and name it "E2HBackup.sql". An example file can be found as an attachment to this document. You should test this script using SQL Management Studio Express Edition (SMSEE) to verify it works locally first.
--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
Note for Windows Server 2008 and Windows 7: The path that you write your backup file to must have the appropriate full access permissions for SQL server to write to it. To avoid this issue, use the above code to write to the default SQL backup path.
Store the E2HBackup.sql file in this location:
C:\Program Files (x86)\Streamside Solutions\Events2HVAC\Scripts\E2HBackup.sql
Open the Task Scheduler. This can be found here:
Start --> All Programs --> Accessories --> System Tools --> Task Scheduler
After the task scheduler opens, create a basic task. Action menu --> Create Basic Task...
Give the task a name.
Enter the trigger:
Enter your specific database backup schedule.
Select the type of action: Start a program
Enter the program to start and the arguments:
Browse 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
Enter the arguments that you will pass to the SQLCMD.exe program:
The arguments field should contain the following items:
-S .\SQLEXPRESS -i "C:\Program Files (x86)\Streamside Solutions\Events2HVAC\Scripts\E2HBackup.sql"
where the parameter .\SQLEXPRESS is the SQL instance on the local machine. If you changed the default instance, adjust this parameter appropriately. This value should match what you have entered in Events2HVAC the Database tab for the SQL Server name in Tools --> Events2HVAC Options, .
After you finish the wizard, right-click on your new task and open the properties.
Select "Run whether user is logged on or not".
Hit OK, then right-click the task and select RUN. Verify that the backup is created in the location you specified in your E2HBackup.sql script file.
0 Comments