Thank you! Thank you!
I just finished reading this
document, which was part of a link in the
recent Buzz newsletter. I have printed it for others to read, especially
those skeptical on the powers of Access and its capabilities.
Darren D.
|
|
Create an Automated Backup of Your Microsoft SQL Server Express
Database
Provided by: John Litchfield, Development Support
Specialist
Have you ever attempted to setup an automated backup of
your SQL Server Express database server, only to discover that the handy job
scheduler maintenance options that you are accustomed to using with the full
version of SQL Server are not available with the express edition? After a
brief search of Google, you may have noticed that others have managed to
implement this procedure, but they either ended up purchasing a 3rd
party product solution or they are using the less than ideal practice of
pausing the SQL Server Express Service in order to create a backup copy of
the MDF and LDF files (this means that the SQL Server is briefly taken
offline). This works, but for servers that must be live on a 24/7 basis,
this is not a solution.
Of course you can upgrade your SQL Server Express to
the full edition of SQL Server; however, this is very expensive. If your
current implementation of SQL Server Express meets your needs with the
exception of this dilemma with obtaining a backup, you will be enthused to
discover that there actually *is* a way to accomplish this task using the
Express Edition (without pausing the SQL Server service and without a
purchase of a 3rd party utility). Simply follow the steps below.
-
Download and install the SQL Server Management
Studio Express interface from the following URL (if you do not already
have this utility installed on your server):
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en
Please note that an installation of SQL Server
Express does *not* install this interface (at the time this article was
created for the 2005 version). This is a separate download.
-
Open the SQL Server Management Studio Express interface.
-
Create a new backup device using the wizard (expand Server Objects =>
New Backup Device).
-
Right click on the new backup device and select the database that you
wish to backup.
-
Select the backup type (most often this is “Full”).
-
Choose a name for the job. Insert a description if you would like.
-
Click the Add button to add a Device Destination (this is where you
would like the backup .bak output file to be placed).
Below is a helpful screenshot showing this Backup Device form.
-
Click Ok. The backup device is now setup. Repeat
steps 3-7 for each database that you wish to backup.
-
Create a batch file using the following syntax
(this is used to execute the backup device). A syntax example is below
(remember to save this file with a “BAT” file extension):
===< Begin Code Batch Script >===
sqlcmd -S MYSERVER\OFFICESERVERS -E -Q
"BACKUP DATABASE MASTER TO TEST"
===< End Code Batch Script >=====..where “MYSERVER” is the name
of the SQL Server machine.
..where “OFFICESERVERS” is the name of the SQL Server.
..where “Test” is the name of the backup job.
..where “MASTER” is the name of the database.
-
Finally, open the Microsoft Windows Scheduler and
assign the batch file created above to a scheduled event. Set the event
to run at the point you wish. For more information on the Microsoft
Windows Scheduler and its useful functionality, please perform a search
of the web using your preferred search engine or consult your Microsoft
Windows documentation.
*Special thanks to Microsoft Technical Support for their
demonstration on how to accomplish this task
Return to the tips page
|
|
