SQL Server 2008 maintenance plans
This document applies to any purchased version of SQL Server 2008 or 2008R2. This does not apply to the Express version.
Your library has invested considerable time and effort in the creation of the library data, so it is vital to protect this investment and ensure that loan transactions and financial records are safe. The most important tool in this process is the Maintenance Plan.
A properly configured Maintenance Plan does more than just backup the databases. It also runs a number of important tweaking tasks which help to keep the system healthy and running as fast as possible.
Third party backup utilities are NOT a substitute for running an SQL Server Maintenance Plan, despite any claims made by their manufacturers.
Make sure that you check to see if any other Maintenance plans are being run on the server. They may be configured in a way that will affect the Amlib databases, certainly you will have to avoid scheduling conflicts.
Step One
In SQL Server Management Studio, expand “Management” and right-click on “Maintenance Plans”, then select “New Maintenance Plan...”:
Give the new plan a suitable name:
One you have clicked on “OK”, this screen will open:
Step Two
From the “Toolbox” on the sidebar, select the “Back Up Database Task”, then drag and drop it into the white area:
Double click on the task to open the configuration settings.
Click on the downward facing arrow on the dropdown box next to “Database(s)” and select the databases to be backed up (all Amlib databases, i.e. AMCAT, AMLIB, AMLOCAL, AMSTATS and AMWEB – yours may have prefixes other than “AM”).
Tick the box next to “Backup set will expire” and set the expiration period based on your IT’s backup policies. If your IT has no policy on how long the backups should be kept, you can leave it at the default 14 days:
Ensure that your Database Backup Task contains the same settings as the screenshot below, then select “OK”:
Step Three
In addition to backing up the entire databases, we will also want to make a backup of the transaction logs. Once again, drag and drop the “Back Up Database Task” from the Toolbox on the sidebar into the white space to create a second task.
To avoid confusion, you can rename tasks by clicking once on the bolded text at the top and altering it:
Double click on the second task to open its configuration settings.
Change the “Backup Type” to “Transaction Log” and again select your Amlib databases from the “Database(s)” drop down:
Like with the full backups, tick the box next to “Backup set will expire” and set the expiration period. Transaction logs can take up quite a lot of room, so OCLC recommend change this to expire every 7 days, but this is dependent on the size of your hard drive.
Ensure that all of your settings as the same as the screenshot below, then click on “OK”:
Step Four
From the Toolbox on the Sidebar, drag and drop the “Check Database Integrity Task” into the white space.
Double click on the task to open the configuration settings and select your Amlib databases.
Ensure that “Include Indexes” is ticked and select “OK”.
Step Five
From the Toolbox on the Sidebar, drag and drop the “Update Statistics Task” into the white space.
Double click on the task to open the configuration settings. Select your Amlib databases from the “Databases” drop down and select “OK”.
Leave the defaults: “All existing databases” and “Full scan”.
Step Six
Drag and drop the “History Cleanup Task” from the Toolbox on the Sidebar into the white space.
Double click on this task to view the configuration settings. Most of them should be left as the default, but you may wish to alter the expiration period.
Step Seven
Drag and drop the “Maintenance Cleanup Task” from the Toolbox on the sidebar into the white space.
Double click the task and set the folder where your backups are getting stored and then set the length of time you would like to keep the files.
Click ok and then copy and paste the maintenance task to create a copy of it and then open the properties and change the file extension to trn to also clean up the old log file backups.
Step Eight
Now that all of the tasks have been created, they need to be connected. When selected, each task will have a green arrow down the bottom:
Click on this green arrow and drag it out to the next task in the list. Do this to all tasks so that they are pointing towards the next one that was created.
The final task will not be connected to a future task.
Step Nine
The final step in settings up your maintenance plans is to create a schedule. At the top of the white space, click on the calendar icon:
It is recommended that these tasks be run daily and set to occur outside of normal opening hours.
Some considerations to be made when scheduling your plan:
- Make sure any scheduled reports have occurred before your Maintenance Plan
- Allow sufficient time for scheduled reports to be completed
- Make sure other system tasks will happen after your Maintenance Plan
- Allow sufficient time for your Maintenance Plan to be completed
After your plan has been scheduled, you can save the plan and exit SQL Server Management Studio.
If you have any queries, please contact OCLC Support.