Backup Database to File - Auto using Vista Scheduler

Last edited by dcostelloe on Sat Jun 04, 2011 1:27 pm; edited 1 time in total

Because I am using Vista for this article you can fill in your own Windows scheduler methods. This sample backups up my Web Site database using SQL Server 2005 SQLCMD tool. This tool also comes with the express version. Ok so lets begin:

1. Check the SQL Forum for the Stored Procedure Code used in this sample
2. Tip: for sql express use: sqlcmd -S.SQLExpress -i<c> Control Panel ==> Adminstration Tools ==> Task Scheduler

6. Select Task Scheduler and then 'Create Task' on the right hand side
7. Create New Task

8. Follow the images below

9. Make sure the user is an Administrator - if not you will be told Cool

10. Set your own time up - This is just a sample

11. Enter Argument as : -S localhost -i "c:\Backup\David\SQL\backupweb.sql"
12. Task Conditions

13. Settings

14.You will need to supply the Administrator account and password to use for the access to the system.This is very important as the scheduler needs to have full access to all folders etc

15. Once accepted by the Task Manager you should see the below:

16. You can test run the scheduler to make sure by clicking on Run on the right hand side
17. That is all that is needed to run an automated SQL Server Scheduled Script

I added to this by Using 2 Free Utilities

For this article you will need to download 2 backup utilities, don't worry they are free and I have tested them both. Because I only read documents as a last resort this Article may change as others find better ways to backup etc.
This time I want not only to backup the database, but also the complete web directory and also the database backup files found in a directory. Wew! sounds like a lot, well it is and thanks to a couple of awesome free utilities by developers we can create this project with ease. Oh and let us also write the completed files to CD!
Get the following utilities:

1. Snap Backup Utility This we will use to compress the web site and include the database backup files while we are at it
2. AceBackup Utility This is the brains behind everything I could use just this tool to do everything, but would rather you figure that out

Ok so ready got the Utilities and installed them? Ok then lets begin:

1. First create a project using Snap-Backup Utility and make sure you allow for Multiple profiles if not you can use the '~' character where I use the profile name. The name of the profile is case dependant. On the command line put exact profile name as you have created!
2. SnapBackup Profiles

2. As you can see I have decided to use multiple profiles as this is not the only backup I will be doing
3. Set the Save Backup file Folder etc. See below image for my settings

[imghttp://www.welford-costelloe.com]/Forums/MySnapBackup.jpg[/img]

4. Do NOT forget to click Save Settings - Do it now Smile

5. Ok that is it for Snap Backup Configuration

6. Configure the brains Utility as we are going to save each backup to a CD also - So start-up AceBackup Utility!

7. Create a New Project: Select File and New and use the wizard (Nice feature)

8. Click Next to begin the 'Master Plan'

9. Enter the name of the backup. Note I am using The AceBackup backup not the archive.

10. Here we now schedule and add the ability to execute Snap-Backup Utility
11. Add the following to Execute Application before processing Project:
java -jar C:Program FilesCenter Key SoftwareSnap BackupSnapBackup.jar BackupWebSite

12. For single profiles use:
java -jar C:Program FilesCenter Key SoftwareSnap BackupSnapBackup.jar

13. Once you click Next you will need to supply an Administrator User and Password

14. Click OK
I am going to execute the Snap Backup utility by command Line as seen above, I have set this to run prior to processing the project in order to create the backup compressed files for the Master Backup to CD.

All done Wizard completed for AceBackup. I'm sure as you have guess by now AceBackup create a Windows scheduler job to run the jobs built inside AceBackup. So lets take a look at some settings:

As you can see in the Task Scheduler there is a new scheduled job for AceBackup. A couple of things that need to change by selecting Properties on the right hand side area.
On Vista make sure you select enable as by default the job is disabled. I changed from Run only when logged on to run logged on or not.
A that is pretty much my current setup for backing up several different items using additional utilities!

	Tweet	David Welford-Costelloe Forums Forum Index -> Databases	Time synchronized with your computer time
Page 1 of 1