Automated Backups with SQL Server Express

If you are running LIMS version 5.0 you are probably aware that it uses a SQL Server backend database. If you work for a larger organization, you may have an IT department that manages your SQL Server installation and takes care of all database maintenance and backup operations. Your IT department’s database administrators likely use SQL Server Agent, which is included with licensed versions of SQL Server, to automate database backups and other maintenance tasks.

If you are using SQL Server Express, Microsoft’s free version of SQL Server, it does not include the SQL Server Agent component of the paid versions. However, you can use a script file with the Windows Task Scheduler to perform automated backups.

The above image shows a script that will back up a SQL Server database to a designated file and folder. The script uses the sqlcmd utility to run a BACKUP command. Download the BackupSQLServerDatabase script from the File Library and save it to a folder on your SQL Server machine.

Much of the script contains text describing its usage. Before trying the script, first open the file in Windows’ Notepad or another text editor. Locate the sqlcmd statement near the bottom of the script. Note that this statement is a single line in the file. The image above shows the statement spanning multiple lines with Notepad’s Word Wrap format enabled.

In the sqlcmd statement, replace MyServer\SQLExpress with the name of your server and the SQL Server instance name. Next, replace the two occurrences of MSClims with the name of your database if it is different. Finally, replace C:\Backup with the location and name of the folder where you want your database’s backup files created then edit the file name if you like. Note that the %mydate% and %mytime% variables in the file name are replaced with the current date and time when the script runs. It is preferable to save the backup files to a folder on a different machine. However, if that is not feasible, just make sure the folder where you save the database backups is included in your system’s backups.

Since you will likely create one or more database backups each day, your designated backup folder could soon contain many large backup files. If you would like to keep only the most recent backups to save disk space, remove the REM at the beginning of the last line in the script then change C:\Backup in the forfiles command to the name of the folder you entered in the sqlcmd statement. This command will delete any backup files with a bak extension in the folder that are more than seven days old. If you would like to keep more or fewer files, change the ‘-7’ in the command to the number of days to keep.

To test the script on your server machine, open a command prompt, use the CD command to switch to the folder containing the script (e.g. CD C:\Backup), then enter BackupSQLServerDatabase and hit Enter. You should get a message showing the results of the backup similar to the screen capture below.

If your edited script works, you can add a job to the Windows Task Scheduler to automatically run the backup at a specific time. You can run the backup while the database is in use so you can schedule multiple backups each day if you like.

To schedule a daily automatic backup, open the Task Scheduler in the Windows Administrative Tools menu on the Start menu, select Create Basic Task, then follow the wizard steps. Begin by giving the task a name and an optional description.

Choose the option to run the task daily then enter the start date and time.

Select the option to start a program then use the Browse button to select your script.

On the Summary screen, enable the option to open the Properties dialog then click the Finish button.

Set your backup task’s security options to run whether the user is logged on or not and to run with the highest privileges to ensure your backup always runs as scheduled. You will be prompted for the user’s password when you exit the Properties screen and save your changes.

To test your new backup task, right-click the task in Task Scheduler and choose Run then verify the backup file was created in your specified folder. You can also view the date and time of the last database backup in the LIMS on the DB Info tab of the System Configuration screen.

If you want to back up your LIMS database more than once a day, simply repeat the steps above and create additional Task Scheduler tasks to back up at other times of the day.