How to Automate the Backup of a SQL Server 2005 Express Data Store - Guest Bloggers - BrianMadden.com
Brian Madden Logo
Your independent source for desktop virtualization, consumerization, and enterprise mobility management.
Guest Bloggers's Blog

Past Articles

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

Written on May 07 2007
Filed under:
167,212 views, 39 comments


by Katie Koepke

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

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.  

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).

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/

 
 




Our Books


Comments

Brian Madden wrote He he... "MetaFrame"
on Mon, May 7 2007 7:26 AM Link To This Comment
I love that the DB instance is called CITRIX_METAFRAME still.. even though we're like two revs past that word.
Dan Shappir wrote Re: He he...
on Mon, May 7 2007 7:51 AM Link To This Comment

Excuse me but what about Wfshell.exe? Doesn't that stand for WinFrame Shell? Thing is, as much as marketing guys like to rename stuff for no good reason, developers know how difficult it is to change the name of internal components without breaking stuff all over the place.

Dan

Ivo Luiz Campagnolo wrote Awesome Article
on Mon, May 7 2007 2:31 PM Link To This Comment
Nice, this is an awesome stuff to general SQL developer's too!
Michael Platsis wrote great stuff
on Mon, May 7 2007 6:08 PM Link To This Comment
very useful, thanks
Guest wrote Re: Re: He he...
on Mon, May 7 2007 10:47 PM Link To This Comment
and how on PS4 you still get a MetaFrame XP logo on logon...
Guest wrote This is much easier/better...
on Tue, May 8 2007 10:20 PM Link To This Comment

The ExpressMaint utility created by Jasper Smith is a much easier way...

http://www.sqldbatips.com/showarticle.asp?ID=29

Regards,

~Andrew.

Guest wrote Use Datareport with Access Password
on Wed, May 23 2007 10:53 PM Link To This Comment

Hello everybody,

I hope you are getting on. I had some problem when i used datareport in vb with Ms Access that has password. I can't connect data to put on datareport. I don't know why so please help me to resolve this problem.

Thanks.

Guest wrote Different filename for each backup.
on Sun, Jun 17 2007 10:04 PM Link To This Comment

Thanks for the nice article but I have one question in mind.

Can we have different filename for each back-up? Maybe date-based file naming (18-06-2007.bak, 19-06-2007.bak, and so on) convention instead of  "MF20.bak"

 

Thank you.

Guest wrote Re: Different filename for each backup.
on Sun, Jun 17 2007 10:15 PM Link To This Comment

Forget about that, I've just run a across a code snippet.

 DECLARE @MyBackupName nvarchar(250)


SET @MyBackupName = 'C:\BackUp\ST_BKUP_' + convert( varchar(10), getdate(), 112 ) + '.BAK'

BACKUP DATABASE [DatabaseName] TO DISK = @MyBackupName
   WITH  DIFFERENTIAL ,
   NOFORMAT,
   NOINIT, 
   NAME = N'DatabaseName-Differential Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD, 
   STATS = 10

Guest wrote automated backup with different filename.
on Mon, Jun 25 2007 4:34 AM Link To This Comment
Hi there, Is there a way i can do an automated backup of a database,
which does not over write the previous nights file, and does not append to it.  
I want the filename to automatically be created and based on the date it was made.
Is there a way to do this?? thanks
Guest wrote automated backup with different filename.
on Mon, Jun 25 2007 4:34 AM Link To This Comment
Hi there, Is there a way i can do an automated backup of a database,
which does not over write the previous nights file, and does not append to it.  
I want the filename to automatically be created and based on the date it was made.
Is there a way to do this?? thanks
Guest wrote Backup data store w/SQL Express
on Wed, Sep 5 2007 12:34 PM Link To This Comment
Will it work with PS 4.0?
Guest wrote thanks
on Sat, Mar 29 2008 5:47 PM Link To This Comment
thanks for the clear information
Cristian Diaz wrote GRACIAS KATIE
on Thu, May 15 2008 1:05 PM Link To This Comment
nice work!!! very useful.....
Guest wrote asdf
on Wed, Sep 3 2008 9:50 PM Link To This Comment

We provide 24 hours outcall service [url=http://www.shanghai-escort.com]shanghai escort[/url], We based in Shanghai but available to travel anywhere in the world <a href="http://www.shanghai-escort.com/" target="_blank">shanghai escort</a>. we should know who r u, so please call me by my telephone No.

Guest wrote asdfa
on Wed, Sep 3 2008 9:52 PM Link To This Comment

We provide 24 hours outcall service [url=http://www.shanghai-escort.com]shanghai escort[/url], We based in Shanghai but available to travel anywhere in the world <a href="http://www.shanghai-escort.com/" target="_blank">shanghai escort</a>. we should know who r u, so please call me by my telephone No.v

Guest wrote Awesome
on Wed, Oct 1 2008 2:30 PM Link To This Comment

Awesome method for automating SQL 2005 backups!!!  Thanks so much!

Guest wrote SqlBackupAndFTP
on Tue, Oct 14 2008 3:56 PM Link To This Comment

SqlBackupAndFTP is way easier:

http://sqlbackupandftp.com/

Guest wrote SqlBackupAndFTP
on Tue, Oct 14 2008 3:57 PM Link To This Comment
SqlBackupAndFTP link: <a href="http://sqlbackupandftp.com/">SqlBackupAndFTP</a>
Guest wrote Easy
on Thu, Oct 23 2008 4:15 PM Link To This Comment

This is simply awesome. Works smoothly as it should.
Why use 3rd party tools? This is way too easy.

Guest wrote can not perform as descried
on Mon, Oct 27 2008 5:52 AM Link To This Comment

 I came across a wired problem in doing this backup that there are only 4  databases lists in the left-column,all of which belongs to system databases, and there is no MF20.dsn visible here,but my farm do working fine now and the MF20.dsn file resides in the default directory.Who can help me troubleshoot this issue?

sandippatil wrote re: How to Automate the Backup of a SQL Server 2005 Express Data Store
on Fri, Aug 21 2009 4:51 AM Link To This Comment

Hi i got error while firing the command from the cmd promt  here below is the command and the error

My server is BMCWEB\SQLEXPRESS

C:\Documents and Settings\Administrator>sqlcmd -S .\BMCWEB\SQLEXPRESS -i "C:\Pro

gram Files\Microsoft SQL Server\MSSQL.1\MSSQL\Everyday DB Backup\DBBackup.sql"

HResult 0xFFFFFFFF, Level 16, State 1

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

shing a connection to the server. When connecting to SQL Server 2005, this failu

re may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

sandippatil wrote re: How to Automate the Backup of a SQL Server 2005 Express Data Store
on Mon, Sep 7 2009 7:15 AM Link To This Comment

Got solution ..

user this command

C:\Documents and Settings\Administrator>sqlcmd localhost  -i "C:\Pro

Alex Green wrote re: How to Automate the Backup of a SQL Server 2005 Express Data Store
on Wed, Jul 6 2011 10:42 AM Link To This Comment

Excellent Script!! wish I'd found long before.

However, I solved this issue by using this tool http://www,sqlbackupandftp.com/ which fits perfectly for SQL Express. The free version lets you schedule up to 2 database backups daily.

josemanuel wrote re: How to Automate the Backup of a SQL Server 2005 Express Data Store
on Tue, Oct 22 2013 2:09 AM Link To This Comment

Thanks for such an awesome article. It helped!

(Note: You must be logged in to post a comment.)

If you log in and nothing happens, delete your cookies from BrianMadden.com and try again. Sorry about that, but we had to make a one-time change to the cookie path when we migrated web servers.

Trackbacks

SBS Backup Utility and SQL 2005 | keyongtech wrote SBS Backup Utility and SQL 2005 | keyongtech
on Sun, Jan 18 2009 12:19 PM

Pingback from  SBS Backup Utility and SQL 2005 | keyongtech

How do I reconnect the database files after a system crash? | keyongtech wrote How do I reconnect the database files after a system crash? | keyongtech
on Fri, Mar 13 2009 6:07 AM

Pingback from  How do I reconnect the database files after a system crash? | keyongtech

Scheduled backup using Microsoft SQL Server 2005 | King Computers Tech Blog wrote Scheduled backup using Microsoft SQL Server 2005 | King Computers Tech Blog
on Wed, Jun 10 2009 2:01 AM

Pingback from  Scheduled backup using Microsoft SQL Server 2005 | King Computers Tech Blog

Stupid Business Server (SBS) wrote Stupid Business Server (SBS)
on Mon, Oct 11 2010 10:19 PM

Pingback from  Stupid Business Server (SBS)

how do i automatically back up my sql server database every month? wrote how do i automatically back up my sql server database every month?
on Thu, Nov 25 2010 1:24 PM

Pingback from  how do i automatically back up my sql server database every month?

Backup Script for SQL, Sharepoint,??Exchange | Smart Business Server wrote Backup Script for SQL, Sharepoint,??Exchange | Smart Business Server
on Fri, Feb 4 2011 9:47 AM

Pingback from  Backup Script for SQL, Sharepoint,??Exchange | Smart Business Server

Backup Script for SQL 2005 Databases, Sharepoint Content,??Exchange 2007 Mailboxes wrote Backup Script for SQL 2005 Databases, Sharepoint Content,??Exchange 2007 Mailboxes
on Wed, Feb 16 2011 5:39 PM

Pingback from  Backup Script for SQL 2005 Databases, Sharepoint Content,??Exchange 2007 Mailboxes

MS SQL Backup Scripts » miopede.com wrote MS SQL Backup Scripts &raquo; miopede.com
on Sun, May 22 2011 11:55 PM

Pingback from  MS SQL Backup Scripts » miopede.com

Backup Script Doesn’t Connect - Windows 8 Guide | Server 2008 | Sql Servers | Tutorial wrote Backup Script Doesn&#8217;t Connect - Windows 8 Guide | Server 2008 | Sql Servers | Tutorial
on Tue, Jun 28 2011 11:27 PM

Pingback from  Backup Script Doesn’t Connect - Windows 8 Guide | Server 2008 | Sql Servers | Tutorial

How to create and automate a SQL Express 2005 backup | The IT Bod wrote How to create and automate a SQL Express 2005 backup | The IT Bod
on Tue, Sep 20 2011 6:30 PM

Pingback from  How to create and automate a SQL Express 2005 backup | The IT Bod

SQL | Pearltrees wrote SQL | Pearltrees
on Thu, May 31 2012 10:43 AM

Pingback from  SQL | Pearltrees

twitter marketing wrote twitter marketing
on Tue, Jul 30 2013 1:43 PM

Pingback from  twitter marketing

how do i automatically back up my sql server database every month? - Just just easy answers wrote how do i automatically back up my sql server database every month? - Just just easy answers
on Sat, Sep 7 2013 4:23 AM

Pingback from  how do i automatically back up my sql server database every month? - Just just easy answers

Alexa Page Rank Booster wrote Alexa Page Rank Booster
on Sat, Oct 5 2013 2:14 PM

Pingback from  Alexa Page Rank Booster