Maintenance Plans with Management Studio

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
10:00
  -
  -
  -
  -
10:15
  -
  -
  -
  -
10:30
  -
  -
  -
  -
10:45

Hello and welcome to another SQL Server Video. My name is Michael Campbell, and in this video we’ll take a look at creating database backups with SQL Server Maintenance Plans.

In this video, we’ll walk through the process of creating backup, or maintenance, plans using SQL Server Management Studio 2008 – though the techniques and approaches that we’ll use apply equally well to SQL Server Management Studio 2005.

Of course, the creation of database maintenance plans plays a very key role in creating a viable disaster recovery plan, so if you don’t already have a good handle on the basics of SQL Server backups, or don’t have a good understanding of how to create a disaster recovery plan, then you’ll want to check out some of the other videos in this series before proceeding.
To get started, let’s open the Management node in SQL Server Management Studio.
And if you’ve got any maintenance plans that you created with SS 2000, and you’ve simply upgraded – those plans will be carried forward and listed here.

Likewise, if you’re really beholden to the approach to creating maintenance plans in step-by-step fashion, you can use the Maintenance Plan wizard to create new maintenance plans with SQL Server 2005 and 2008 – where it’s possible to create plans using a process that’s very similar to what you’d use with SQL Server Enterprise Manager.

Although, it’s worth pointing out that you’ve still got a lot more functionality and options at your finger tips – even when going this route.

Otherwise, SQL Server Management Studio provides greater flexibility when it comes to creating maintenance plans by letting you use a powerful designer that’s similar to what you would use when creating SQL Server Integration packages – as you can see here.

And the first thing to note when it comes to creating maintenance plans with this new approach is that we’ve got a design surface out here in the main work area, along with a toolbox containing tasks that we can just drag and drop on to the surface.

And what’s great about this approach is that it provides you with some incredible flexibility – as we’ll see a bit later on, and it’s also very discoverable – meaning that it’s pretty easy to figure out what all of the options do, just by playing with them.

A huge thing to note though, is that up here, above the designer, we’ve actually got the option to create subplans.

Personally, I think that this is easily one of the coolest features of this new approach, as it lets you logically group related tasks into a single maintenance plan, or package – without forcing you to bundle all of the details into a single, big ugly, mess down in the designer.
More importantly though, note that each subplan can be given its own schedule – which is very important, as it means that you can do something like create a maintenance plan for a single database, and schedule different times for full backups, for differential backups, and for transaction log backups.

And, if you want, you can also create sub-plans for actual maintenance tasks too. Though, personally, I like to think of maintenance tasks and backup tasks as being two totally separate things.
Yes, they’ve both vitally important to success, but I prefer to create distinct maintenance plans for maintenance tasks and distinct plans for disaster recovery tasks like backups.
Doing so makes it lots easier to discern what kind of a problem I’m dealing with when troubleshooting a failed job – as I instantly know whether I’m dealing with a failure of a maintenance task, or of something that’s affecting my backups.

To get started with a sample maintenance plan, let’s drag a backup database task from the toolbox onto the designer, or work area. By double-clicking on it, we’re able to start working with task properties and customize them to fit our exact needs.

Up near the top of the task dialog, we’re able to specify connection information – meaning that we could orchestrate backups on a remote box using this task if desired. We’re also able to specify the type of backup we’d like to create.

In our case, we’ll create a maintenance plan with full and transactional log backups – so, in this task, we’ll opt for full backups – though, it’s important to call out that with SQL Server Management Studio you can easily schedule differential backups – which simply wasn’t an option with SQL Server 2000.

Then, we need to select which database, or databases, we want to backup. And the key thing to watch out for here though is avoiding the temptation to create a single maintenance plan for all of your databases – because if you treat all of your databases the same in terms of maintenance and disaster recovery, you’ll likely find that you’ll have too much protection and coverage for databases that don’t matter very much, and not enough coverage and protection for databases that matter the most.

Therefore, don’t be afraid to create one-off backup plans for each database, or to group common or similarly important databases into a couple of different plans.

And with that in mind, in this example, we’ll just select the SSV database, and click OK.

We’re then presented with the option to backup either the entire database, or just selected files or filegroups – and this is another powerful and great addition introduced in SQL Server 2005.

We can also set the expiry for our backup, but – as we’ll see a bit later, this isn’t quite as intuitive as you might think it is – meaning that backups won’t be automatically removed from your server once they expire.
At this point, we then have to deal with 2 core options – whether we want to backup to a device, or to individual files. In two previous SQL Server Videos, SQL Server Backup Options {bopt}, and Creating Backups with SQL Server Management Studio {???}, we covered the differences between both of these approaches in detail.

For now, however, suffice it to say that when you use a device, you’re creating a file location that acts as a ‘bucket’ (or device) which can contain multiple different types and instances of backups – even from different databases – which is why the dialog says: Backup databases across one or more files – as the files you would select here, are devices, and act as containers for individual backup operations.

Whereas, if you use the Create a backup file for every database option, each distinct backup will be represented by a single file.
Both approaches have strengths and weaknesses, though using individual files per each backup is usually the easiest route to take – especially if you drop each backup into a dedicated sub-directory – as that makes it easier to manage and locate backups.

But don’t discount devices either – as they provide some great options when it comes to enabling redundancy {see ???? for more information}, and with SQL Server 2005 and above, configuring these options has never been easier.
Finally, if you’re using an Enterprise Edition of SQL Server 2008, you can also opt to compress backups as they’re written to disk which is something you’ll absolutely want to do if you’re not already using a 3rd party backup solution.
And, with our settings configured, we can go ahead and click OK.

Now, to prevent our backups from slowly accumulating, and blowing the server out of disk, we’ll also want to drag out a Maintenance Cleanup task onto the designer.

And with a second task in place, we can get a real sense for just how flexible these new plans can be. For example, in most cases, it’s usually a good idea to have your backups execute successfully, and THEN remove any older backups. But in some cases you may want to remove older files BEFORE your backups actually execute.

And what’s nice about this designer, is that you get complete control over the order of operations – with all of your tasks. It’s also possible to make sure that multiple tasks complete BEFORE a single task executes by simply dragging precedence operators (or green lines) from multiple tasks into a single task – like so.

In our case though, we’ll execute the cleanup after our backups, and then double-click on the task to configure properties.

Cleanup tasks provide either-or options – meaning that we can either clean-up text reports or backups – but if we want to do both, we’ll need two different tasks.

At this point though, we’re just looking to clean up older backups – so we can easily configure that as needed – as this dialog is very self-explanatory.

That said, I personally like to give my cleanup operations a tiny bit of a buffer.
And I do this by converting days, for example, into hours – and then padding them just a bit as this helps make sure that I won’t nuke a backup in cases where they might run long one night, and short another, and so on.

With our full backup and cleanup operations configured, we just need to schedule when we want these operations to occur. For databases smaller than a few hundred gigabytes, you’re typically going to want to do full backups every night.

And the reason for that is that if you’ve got databases of that size, you’ve typically got enough disk speed and space to handle those kinds of backups on a daily basis.
Therefore, in our case, I’ll just set this backup to run daily, at … 2AM-ish – and make sure that there’s no end-date.
Then we’ll want to create another sub-plan – this one for log file backups. And in terms of scheduling, there’s really no reason you should be doing transaction log backups longer than 15 minutes apart.

That may sound like you’ll end up spamming tons of files onto your server, but which would you rather deal with: roughly 100 transaction log backup files per day, or the ‘pleasure’ of trying to re-enter more than 15 minutes worth of lost data?

Likewise, in more critical environments, don’t be afraid to run transaction log backups every 2 or 3 minutes.
The performance overhead for executing a t-log backup in the VAST majority of cases is so minimal that you’ll never feel it, and routinely keeping your transaction log backed up will help keep your data that much more protected. It will also help to keep your actual transaction log file more in check in terms of size.
In other words, try to fight the urge to ‘restrain’ how frequently you’re backing up your data. There’s really a very minimal performance impact involved when it comes to any kind of backup, and each time you’re ‘conservative’ of system resources, you’re usually just doing yourself a disservice for when a crash actually occurs.
With a plan in place that handles full and transaction log backups, we’re now ready to save our maintenance plan, close it, and then switch over to the SQL Server Agent tab to get a feel for how our subplans are executed under the covers.And, as you can see here, if we open up one of these execution steps, or subplans… we can look at general information, and even configure alerts and notifications.

We can also make changes to schedules and our changes will be reflected back in our maintenance plans as well.

Best of all though, by creating these maintenance plans, we’ve created scheduled jobs, or tasks, that will execute automatically to help handle backups – which represent a huge component of any viable disaster recovery plan.
And with that, we’re done with this video – thanks for joining us and we look forward to seeing you in subsequent videos where we cover how to restore and recover from backups, as well as where we look at troubleshooting maintenance plan and backup failures.