Creating a Backup Maintenance Plan

The Maintenance Plan feature lets you set up an end-to-end job process that will provide a backup job, predicated with various database verification routines, such as database integrity and index tuning, that can be appended with file clean-up and database backup verification.

To create a maintenance plan you can use the Maintenance Plan Wizard or drop plan components onto a Design “palette” as described in Chapter 4. The Wizard is easy to use and once a plan is created you can always go in and change the plan on the Design palette. To create a maintenance plan for backing up databases and transaction logs, do as follows:

1. Expand the Management node in SQL Server Management Studio, right-click on the Maintenance Plans node and select Maintenance Plan Wizard. The wizard starts and asks for a maintenance plan name. Provide a name that describes the databases and procedures you will perform. Click Next, the Select a Target Server dialog box loads. See Figure 7–8.

   
   Figure 7–8: Selecting the target server for the maintenance plan

2. In this dialog box you can provide a description for the maintenance plan. Also choose the security information for the logged in user from Active Directory. You can also choose a SQL Server user account. Click Next.

3. You can now select a task from the predefined task list as shown in Figure 7–9. Choose items like “Back Up Database (Full)” and then click Next.

   
   Figure 7–9: Select a maintenance task

4. The Select Maintenance Task Order dialog box now loads. Here you can choose the priority or execution sequence of the various tasks on offer. After choosing the order of task execution, you can set various options.

5. In the Databases drop-down list, choose the database to backup. You can choose all or just a selection of databases to include in the full backup process. From here on out, choosing the database type, schedules, and post operations is pretty straight forward. You will get a chance to define various SQL Server Agent actions combined with the alerts, notifications, and operators you set up under SQL Server Agent, as described in Chapter 4. Once you save the plan, you can find it as a leaf under Maintenance Plans.

The plan wizard makes it easy to create something fast and on the fly but it is not best for more advance scenarios and for extending or adding to existing plans. For this we must open up and work a plan interactively

Sleeping at Night

The life of a DBA is filled with surprises. I have often had the strangest requests from users and seen some really interesting stuff. One scene I will never forget is the letter opener I found jammed into one of the DSS tape drives one morning, sticking out of the server by at least a foot. It appeared that the night operator was trying to remove a jammed cartridge and had no luck dislodging it. The morning log caused a lot of chuckles because the only problem in the report was “one of the servers has been impaled by a letter opener.”

But one item on your list of things a DBA has to do that should be no surprise to you is recovering a database. As sure as the sun will rise in the morning, there will come a time when frantic executives come pouring into your office like Federal agents stalking America’s most wanted. If you plan well and practice disaster recovery often, you should have no problem sleeping at night.

When I say “practice,” I mean that you should set up development servers, wreck them, and then recover them. Trash your databases, the server binaries, and even the operating system and then restore the whole caboodle to the last transaction. Try all forms of backup and restore, and also delete transaction logs and so on.

Only then when the bits and bytes hit the fan will you be able to calmly say to the CEO or CFO “no problem, I will have you back in a jiffy.” Woe upon the DBA who sits down at his or her smoking server spewing out the last sparks of a hosed disk array and saying “uh…now what.”