Each site is responsible for backing up their servers. SQL databases are typically not backed up by regular backup software.
What follows is a step-by-step procedure to create the jobs to backup all SQL databases every day and to run integrity checks to verify the status of the databases.
Please backup ALL databases EVERY day. With the exception of the FaxAction database, these are all small, so the time and space required to perform the backups is minimal, but recovery is VERY difficult without these backups, especially the MASTER and MSDB databases.
To begin, open the SQL Enterprise Manager, right-click on Maintenance Plans, and choose New:
Choose NEXT then ALL DATABASES.
Choose NEXT again (don’t update data Optimization Info in this Plan).
Choose NEXT
Choose CHECK DATABASE INTEGRITY
Leave INCLUDE INDEXES checked
DO NOT check ATTEMPT TO REPAIR (since it has to be in single user mode it will always fail)
Schedule DAILY at 12:01 or some off time when server is idle
Chose NEXT and add the backup portion of the plan. Schedule this a few minutes after the integrity check. How long after will depend on how long the integrity check takes.
When scheduling verify the ENABLE SCHEDULE box is checked!
Choose NEXT and pick a directory on a drive that has a lot of space, and preferably not the same drive the database is on. On a clustered system this may even be on the other server’s unmirrored (not C) drive. This directory MUST be backed up to tape!!!
Create separate directories for convenience in checking these and keep the older ones around long enough to be useful without filling up the drive (at least 2).
Get the transaction logs too:
Store them in the same directories as the databases.
Choose next and record some logs so we can find out what happened if something bad happened:
Notice you can Email to an operator. The SQL MAIL needs to be setup and this then should be tested and sent to someone who cares. An operator for instance may have to log that a good or bad message came on his shift.
Give it a name and finish. If you have made a mistake, it is strongly suggested that you delete the maintenance plan and recreate it.
Now go to jobs and look at the fruits of your labor:
You should check these daily unless you are getting an email. The icons will not be the pretty blue, so you can tell right away!
You can manually (re)run these by right clicking and choosing JOB HISTORY
 | There maybe other maintenance plans you want to set up. Please keep these separate. |
| Please create the plans as shown. Different steps may trigger other parameters to be set, which may exclude a backup. For instance, if you set the “Perform these tests before doing backups” AND the integrity check finds problems it will not back up the database. If you attempt |
| Let a cycle pass and convince yourself that these tasks all completed successfully. |
You are HALF done. Now you have to confirm they are backed up to Tape.
Your nightly tape backup of the FaxAction server(s) should back these up, just like any closed file. This needs to be confirmed.
You will also notice some jobs that perform regular maintenance on the FaxAction database, these need to occur regularly, and if you need to modify the schedule, make sure they don’t run during the day, the backups the other tasks, etc.