This article is for users who use XPressEntry with a SQL Server Express database engine and would like to automate the backup of their database. This is not for users who have a full Production SQL Server backend or for users who schedule backups of the entire windows environment. Production SQL Server databases have a simpler tool through the Maintenance module in SQL Server Management Studio.
These instructions are based on this Microsoft article (https://docs.microsoft.com/en-us/troubleshoot/sql/admin/schedule-automate-backup-database) but is tailored to XPressEntry. Download the links from the instructions below, rather than the Microsoft article, because some of the downloads have been updated.
To get started on automating your backup, download the following SQL Stored Procedure- https://raw.githubusercontent.com/microsoft/mssql-support/master/sample-scripts/backup_restore/SQL_Express_Backups.sql
Run this sql command on your master database. It will create a stored procedure that will back up your database when called.
If you are using SQL Express 2016 or later, download and install this- https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility. Earlier versions can skip this step, as it was included in the SQL installation by default.
Open notepad or your favorite text editor. Paste the following line in it.
sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @databaseName=’XPressEntry’, @backupLocation=’C:\SQLBackups\’, @backupType=’F’“
Update the line above in your text editor with the following information.
.\SQLEXPRESS – Replace this with your SQLMachineName\Instance.
@databaseName=’YourDatabaseName’ XPressEntry is the default.
@backupLocation=’Replace with your folder path where backups will be stored’
@backupType=’F’ F for full backup, D for Differential backup
Save this file as XPressEntryBackup.bat in a location where it won’t be accidentally removed.
In windows, open Task Scheduler. Create a new task that will run a program.
Select the user that the task will run as. This user need to have proper backup permissions in your SQL Server database, or system admin rights. This user will also need write file permissions to the backup location.
Next, select how often the task should run.
Lastly, select the action that the task should take. Click the “Actions” tab, then the “New” button, and finally browse to the location where you saved XPressEntryBackup.bat
Create the task and enter the password for the user account the task is running under. Test the task by selecting it in the task list, right clicking it, and selecting Run. It should create a backup of your SQL Server database with the timestamp of the backup.
