December 14, 2012 09:31 by Jervis
Microsoft’s SQL Server Express is a fantastic product for anyone needing a relational database on a limited budget. Server Express is free but it comes with a few limitations such as only utilizing 1 GB of RAM, databases are limited to 10 GB, and it does not include SQL Profiler. For low volume sites that do not need enterprise level capabilities, this is a compelling solution. Here is a complete SQL Server feature comparison of all the SQL Server editions.
There is one other important limitation that needs to be mentioned which is that SQL Server Express does not include SQL Agent. This means that you can not schedule administrative tasks such as backing up your databases. As everyone knows backing up data is critical and with SQL Server Express you can still backup your databases but you have to do it manually. So what does one do if you don’t have a budget to license SQL Server but you need scheduled backups of your databases?
The answer is SQL Scheduler. SQL Scheduler is a simple lightweight application that installs on your server and runs as a service. Using the application’s GUI you can create multiple jobs to run your backups on a predefined schedule and you can even configure it to send you an email notification if it completes or fails to run properly.
After you download the program, unzip the archive and run InstallService.bat file from the command prompt. The installation will complete quickly.
Once you launch the program you just need to connect to your local SQL Server Express instance on your server. Here is how it will appear without any jobs configured.
From the File menu create a new job. In this example we’ll create 1 job to take a full backup of each database that is configured on the server.
Click on the Details tab and you’ll be able to enter your SQL statements for whatever task you’re trying to accomplish. Be sure to edit the script and change the path of where you want the database backups stored on your server.
On the schedule tab you can define the time and date of your job will run.
To have the job send you an email upon completion or in the event of a failure open the SQLScheduler.WindowsService.exe.config file and enter the mail server address along with the and username and password for authentication.
<smtp deliveryMethod="Network" from=”[email protected]”>
<network host="locahost" userName="x" password="x” />
Once the job runs a full backup will be created for each database and it will be stored in a subfolder of the path you specified in the script.
Now that your daily full backups are being created locally on the server the next step would be to either configure Windows Server Backup or configure 3rd party online backup solution.