Scheduling Regular Backups of your ILLiad Database

Set the schedule so the job is recurring. You can set up whatever schedule you want, but ideally, you should have one job that appends to the file and another job that overwrites that file once a week. For example, you can have the first job run daily at 10 pm and append to the file, changing it to append by selecting Append to the existing backup set on the Options page. Then make a second job that writes to the same file and overwrites it once a week, says Wednesday at 6 am. This would provide a backup file that had several database copies in it from each night (to allow you to catch a change that happened several days ago) and then overwrites it once a week to prevent the file from growing too large.

You can schedule your backup to overwrite each night as long as you are then copying that backup file to another location that would have a week's supply of backups. So if you backed that file up to tape each night after the SQL backup job ran, you would not need to append to the SQL job (using overwrite options) within Microsoft SQL Server.

To schedule regular backups of the ILLiad database, you will need to create a backup as described above and script it to a job. To do this:

1. Following the directions above, fill in the needed information for creating a database backup.
2. On the Backup Database screen, click the arrow next to the Script icon and select Script Action to Job.
3. On the New Job form, create the job by completing the necessary steps under Select a Page (General, Steps, Schedules, etc.). You will need to give a name to the job, its steps, and the schedule involved (Daily at 10 pm, for example).
4. When you are finished, click OK to save the job and close the form.

Viewing/Managing Backup Schedules

To view any scheduled backups you may have in Microsoft SQL Server Management Studio, go to SQL Server Agent: Jobs. Any scheduled backup jobs will be listed here. Right-click and select Properties on a job to see its properties. You can change the schedule for that job or add notifications for success and/or failures. The SQL backup job itself is a SQL script stored in Step 1 of the Steps. An example of the script is:

BACKUP DATABASE [ILLData] TO DISK = N'c:\illiad\backup\ILLDataBackup.bak' WITH 
INIT , NOUNLOAD , NAME = N'ILLData Backup to Hard Drive - Overwrite', NOSKIP , 
STATS = 10, NOFORMAT

The job above is backing up to c:\illiad\backup\ILLDataBackup.bak and overwriting that file (WITH INIT). The name of the job in SQL is "ILLData Backup to Hard Drive - Overwrite." The only difference you may see for the other jobs would be the tag for appending (WITH NOINIT) instead of overwriting (WITH INIT).

Make sure that your SQL Server Agent is running or none of the scheduled backup jobs will execute. You can check that service under Services in the Control Panel | Administrative Tools. Make sure that SQLServerAgent is set to Automatic startup and is currently running.