How to Automate the Backup of a SQL Server 2005 Express Data Store

I recently built a Presentation Server 4.5

I recently built a Presentation Server 4.5 (PS 4.5) farm where the decision was made to use a SQL Server 2005 Express database as the IMA data store. When it came time to create an automated backup of the data store, I soon realized the process was not at simple as creating a SQL job in a friendly GUI as with a full-blown SQL Server install, or running DSMAINT BACKUP. This article describes how automate the backup a SQL Server Express data store without having to purchase a third-party tool or without having to learn SQL programming. That said, this document is intended for the non-SQL savvy administrator unfamiliar with creating T-SQL statements.

As you may know, it’s very easy to install and use a SQL Server Express database as your data store since Citrix provides at custom batch file in the PS 4.5 Support folder. Running \Support\SqlExpress_2005_SP1\SetupSqlExpressForCPS.cmd automatically installs and configures a SQL Server Express instance named CITRIX_METAFRAME. By default, three SQL Server Express configuration tools (SQL Server Configuration Manager, SQL Server Error and Usage Reporting, and SQL Server Surface Area Configuration) are also installed on the server, but none of these can be used to configure a backup.

Creating the Database Backup Script

For further SQL Server Express management options download and install SQL Server Management Studio Express .  This is a free tool from Microsoft and will be used to create a script to backup your data store.

  1. Once the tool is installed, launch Microsoft SQL Server Management Studio Express and connect to the CITRIX_METAFRAME instance.
  2. Expand Databases and you will see the MF20 database. (This is the default name of the database that was created when creating the Presentation Server farm.)
  3. Right click the MF20 database and select “Tasks | Backup”.  A dialog box appears allowing you to define different options such as what type of backup (full or differential) you want to do, backup destination etc. Configure the available options as desired then click the “Options” page on the left-hand column. Continue configuring options accordingly. For example, you may want to select “overwrite all existing backup sets.”
  4. Once all desired options are set, select “Script | Actions to File” and enter a desired file name, for example, “DatastoreBackup,” and specify the location where to save the file.

This creates a .SQL file which scripts the options you defined in the prior step. The contents of your .SQL file may look like this:

 


 

BACKUP DATABASE [MF20] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MF20.bak' WITH NOFORMAT, INIT,  NAME = N'MF20-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

To test your .SQL file run the following from a command prompt.

sqlcmd -S .\CITRIX_METAFRAME -i "C:\<enter path to .sql file>\DatastoreBackup.sql"

If the MF20.bak file was created with the correct data and time stamp then you know your script works. By default the MF20.bak is located in C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\Backup. (This folder might be “MSSQL.1” or “MSSQL.x” depending on what else is on your server.)

Automating the Database Backup

You can automate the backup process by creating two Scheduled Tasks.

SQLCMD Scheduled Task

  1. First, create a Scheduled Task to automate the .SQL script created above. Use the Scheduled Task Wizard and when asked to select a program browse to use browse to C:\Program Files\Microsoft SQL Server\90\Tools\binn\ SQLCMD.exe. Define the Schedule Task parameters accordingly and click “Finish”.
  2. Go the properties of the newly created Scheduled Task and edit the Run command as such.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\CITRIX_METAFRAME -i "C:\Program Files\Microsoft SQL Server\DatastoreBackup.sql"

Copy MF20.bak Scheduled Task

copy "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MF20.bak" "\\<servername>\<sharename>\"

Lastly, create a simple Command Prompt Scheduled Task and configure accordingly. (Make sure to run this Scheduled Task after the SQLCMD Scheduled Task.)  Go to the properties of the newly created Scheduled Task and edit the Run command to point to the location of CopyMF20bak.cmd (or your respective batch file name).

Next, create a simple batch file to copy the MF20.bak from the local server to a network share located on server being backed up regularly.  For example, create a file named, “CopyMF20bak.cmd”, with the following contents.  

Resources

http://www.sqldbatips.com/showarticle.asp?ID=27
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=487021&SiteID=1
http://msdn.microsoft.com/vstudio/express/sql/download/

25 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchVirtualDesktop

SearchEnterpriseDesktop

SearchServerVirtualization

SearchVMware

Close