Automating SQL Server Express Backups
Last Updated: 2019-06-05
- Guard1 Plus versions: 5.x
- Guard1 Plus SE versions: 4.x, 5.x
Guard1 Plus version 5.x uses SQL Server Express exclusively, and Guard1 Plus SE supports SQL Server Express.
SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. While manual backups can be performed with the Guard1 Plus Data Maintenance Application or via SQL Server Management Studio for Guard1 Plus SE users, a different approach is necessary to automate backups for these products.
For Guard1 Plus SE users who wish to have direct control over the process, Microsoft KB Topic 2019698 describes how to use a Transact-SQL script together with Windows Task Scheduler to automate backups.
For Guard1 Plus and SE users who are willing to use a third-party utility, TimeKeeping Systems suggests SQL Backup Master from Key Metric Software. This article describes how to configure SQL Backup Master to create a scheduled backup of the Guard1 Plus master database.
- Reference herein to SQL Backup Master and Key Metric Software does not constitute or imply its endorsement, recommendation, or favoring by TimeKeeping Systems, Inc.
- TimeKeeping Systems is not affiliated with Key Metric Software, the makers of SQL Backup Master, and therefore cannot provide technical support for the SQL Backup Master product. If you encounter problems with SQL Backup Master, refer to the product's Help file or contact Key Metric Systems.
- There are a number of third-party options for SQL Server Express backup automation. TimeKeeping Systems has tested SQL Backup Master and found it to be appropriate and safe for use by our customers.
Obtaining SQL Backup Master
SQL Backup Master can be downloaded from http://www.sqlbackupmaster.com/download.
It should be installed on the Guard1 Plus PC, or on the SQL Server for Guard1 Plus SE.
Configuring SQL Backup Master
Guard1 Plus uses proprietary passwords for SQL Server database access, therefore the procedure below requires remote access by Technical Support to the Guard1 Plus PC to configure the SQL Login(s). If you would like to set up SQL Backup Master please contact Technical Support.
SQL Backup Master requires the following:
- A Windows account that is a member of the SQL Public role.
- This SQL Login must have db_backupoperator membership to the database(s) to be backed up.
- A Windows account that has Modify or higher permissions to the folder where the backup files will be delivered. This can be the same account used by SQL, or a different account.
Once the SQL Login is configured (or you've decided to use an existing SQL login with sufficient permissions and the folder permissions are confirmed, set up the backup job:
- Open SQL Backup Master.
- Click the Backup and Restore tab.
- Click Create new database backup.
- In the Source pane, click Choose SQL Server...
- Click the Server name dropdown to search for SQL servers, or enter (local)\GUARD1PLUS.
- Select Connect using Windows Authentication.
- NOTE: You can select Connect using the following SQL Server account and enter the SA credentials.
- Click OK.
- Select the Guard1Plus database.
- Only select the Guard1PlusConfig database if there are numerous application user accounts.
- In the Destinations pane, click Add.
- Choose Local or Network folder and click Select.
- Browse to the folder, or type the UNC path in the field.
- NOTE: Mapped drives are not recommended because they may not be mapped for all users of the PC. A full UNC path (e.g. \\PCName\ShareName) is recommended.
- If necessary, enter Windows credentials to use to connect to the destination folder. Click Test when finished to confirm the connection.
- Click OK.
- In the Configuration pane, click the hyperlink next to Schedule:.
- Select Full Backup, enter the schedule parameters, and click Save.
- NOTE: By default the job runs as NT_AUTHORITY\SYSTEM. This may be sufficient. Test the job before changing this setting. To change:
- Click the hyperlink next to Job runs as (?):.
- Select Run backup as different Windows user.
- Enter the account name (e.g. Domain\User).
- Enter the password.
- Click Test.
- If the test is successful, click Save.
- In the Backup name field, enter Guard1 Plus Master Database Backup.
- Click Save.
- On the main screen, click Back up now to confirm the process works.
The basic setup is the same as described above. The account used to connect to SQL must have db_backupoperator membership to the database(s) to be backed up. The account used to run the job must have Modify permissions to the destination folder.
For SE, SQL Backup Master does not have to be installed on the SQL Server - it is capable of backing up databases remotely.