SQL Express 2005 automatic database backup

[Product_Support]

Issue

One of many methods that can automatically backup SQL Express 2005 databases

Product Line

Andover Continuum

Environment

• SQL Express
• Continuum Cyberstation Standalone

Cause

Some customers are looking for a way to automatically have a database backed up on a schedule. Since the free limited version of SQL express that comes built into Continuum for smaller site setups does not come with a front end, backups of the database must be performed via windows command line utility.

There are commands available to run at the windows command line but this article will help with performing this operation automatically and on a schedule of your choice whether is be daily, weekly, monthly. This is accomplished by creating a  batch file and using the windows task scheduler. Help on using the task scheduler and also creation of batch files can be found on a simple Google search. In the resolution section below we give one example of how to set this up.

Resolution

1. Open up notepad and paste in the sample query below. Please note that you may need to change the query a bit to adjust to your sites needs.
2. Here is the query. Please note the bold and underlined text. You will need to put your machine name and also your SA password in the bold and underlined sections. Also notice where the words backup database continuumdb is in the query. Your database might be named something else, but you can find that out by viewing the database tab of the Continuum database initializations workstation editor. Lastly notice that the location of the backup and the name. In this example there is a directory named DBbackup that you'll need to create for this example or create your own directory and change the path in the query. The name of the backup file is called MyBackup.bak. You will need to change this name to something that is meaningful to your site.

   • The switches below are -S for the servername and in the case of type of command line query -S is your machine since it is the server locally.

   • The switch -U is username and we are using the System Admin account for SQL that was created when the instance was created.

   • The switch -P is password and in this example we are using the sa account so please put in the sa password that you created when first installing SQL express 2005.

   • The switch -Q means command line query and notice that everything in double quotes will be the query. Also notice the single quotes for the path that the database backup will be created at.

     sqlcmd -Sputyourmachinename\sqlexpress -Usa -PputyourSApassword  -Q "backup database continuumdb to disk='C:\DBbackup\MyBackup.bak' with init"

3. Once the query and been corrected and pasted into notepad, save the file as something meaningful like DBbackup.bat notice that the notepad file is not saved as text but as a *.bat file which is a batch file that can be double clicked on to execute. It is advised to test this batch file now to make sure it works. Go to your backup directory and check to make sure you have a backup file.
4. Now the only thing left to do is create a windows scheduled task.  In order to accomplish this you will need to
   1. Select Start/All Programs/Accessories/system tools
   2. Click on Task Scheduler (this path may vary depending on which windows OS you are running).
   3. Under the Actions menu item select Create basic task.
   4. Follow the prompts giving your newly created task a name, date to run, time to run
   5. Select program and browse to the location you have saved your DBbackup.bat file to.
   6. Once created it will run on the time and dates you have selected. You can also select your task in the Task scheduler and right click to run now for a test.

IMPORTANT NOTE:

Please keep in mind that automatically backing up the database using this method will continue to replace the old backup and create a new backup. it is highly suggested to always verify the backups and maintain enough disk space to complete these tasks.