|
Free Resources from FMS |
|
|
|
|
"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. |
|
|
|
|
Microsoft
Access Resource Center
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):
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).
-
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.
..where “OFFICESERVERS” is the name of the SQL Server Database.
..where “Test” is the name of the backup job.
-
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
