Maintenance Plans with Enterprise Manager

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:27

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.

And to do that, in this how-to video, we’ll walk through the process of creating a sample backup, or maintenance plan, and highlight some of the key things to watch for and take advantage of when creating your own maintenance plans with SQL Server Enterprise Manager.

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, we’ll begin with Enterprise Manager where we’ll open the Management Tab and then click on the Database Maintenance Plans node.

Then we’ll right click on the maintenance plans node to create a new maintenance plan from scratch.

And on the first page the first thing we’ll need to do is specify which databases we want to include as part of this maintenance plan.

As you can see, we’ve got a couple of simple options, as well as a more customizable option – where we’re allowed to specifically select exactly which databases we want to use.

Personally, I like to use the All system databases option to create a system databases maintenance plan – as that’s an easy way to make sure that core databases which are key to continued server operation are regularly backed up.

But, when it comes to non-system data, I like to specifically select databases and I typically create either a couple of different, or distinct, plans to either cater to different types of databases (such as critical databases, dev databases, and so on).

Or I’ll even create one-off maintenance plans for really important databases if necessary.

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 mean the most.

So, in this case, we’ll just create a custom maintenance plan for the SSV database, and click next.

At this point, we’re then greeted with the option to begin configuring maintenance tasks.

And, while I definitely recommend creating maintenance plans that actually utilize most or all of these options (depending upon how critical your databases are), I personally prefer to handle maintenance tasks in dedicated maintenance plans as that makes troubleshooting failures that much easier – and also helps ensure that if an error occurs while processing maintenance tasks you won’t run the risk of your backup not kicking off or starting.

So… with that in mind… we’ll go ahead and skip this page, and the next – as they’re solely focused on maintenance tasks – and our purpose here is to create backups as part of a reliable disaster recovery plan.

With maintenance plans, creating routines to enable full and transaction log backups couldn’t be simpler – and, as you can see from this dialog, all we need to do to get started is specify that we want to backup the database, and then coordinate when we want this to happen.

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.

And, in a sense, backups are free – meaning that there’s really no cost, other than some slight performance overhead, and the disk space that it takes to store your backups – so you might as well provide yourself with some solid coverage by backing up whenever possible.

Therefore, in our case, we’ll just set this backup to run daily, at … 2AM-ish – and make sure that there’s no end-date.

Once we’ve scheduled when we want this backup to occur, we can then specify some additional options – such as where to drop our backups, and so on.

Personally, I really like being able to put the backups for each database into their own specialized folders – as it makes it much easier to visually interact with the ‘inventory’ of backups that you’ve got available when it comes time to either restore your databases, or when looking at recouping disk space by cutting back on the number of backups if you start to run low.

As for where you store your backups, it’s possible to drop them on-box – and you’ll want to put them some place redundant (meaning on a RAID1, RAID5, RAID10 and so on).

Otherwise, if you don’t have lots of activity on your box when the backup is being made, you can effectively store your backups on the same drives as your data files – and this works pretty well on most systems where databases aren’t much larger than 50-ish gigabytes. Otherwise, you may need to create specialized volumes specifically for backup – and push your backups there.

You can also push backups off box, by specifying a UNC path - and this can be a great option if you’re running low on disk on your local box.

The only drawback, of course, is that if you need to restore and recover a database, you’ll need to suck backup files across the wire – where you’ll get about 40GBs per hour of throughput on megabit networks, and about 400-ish Gigabytes per hour of throughput on gigabit networks.

Then, one of the greatest things about SQL Server 2000 maintenance plans is that they let you remove older, or ‘expired’ files. And, with this option, it’s pretty easy to create ‘rolling’ backups that help let you juggle the amount of backup protection you’ve got against the amount of free space available on your system.
As such, I typically like to keep 2-3 days worth of full backups in place for most key databases, and in order to give myself a bit of a buffer (in case those backups start to take a while), I always convert backups to hours, and give myself a tiny bit of padding over what I’m shooting for. So, for example… instead of entering 3 days, I’ll convert that to 72 hours, and then add 2hours as a buffer.
And, as you can see, you can also specify which extension to use. Anything technically works, but .BAK is used by convention and there’s really no need to change it.

Then, after specifying details for full backups, we can do the same thing for transaction log backups – which, obviously, only works if the database you’re backing up is using full or bulk-logged recovery.
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 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 and will also help to keep your actual transaction log file more in check.
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 backups, and each time you’re ‘conservative’ of system resources, you’re usually just doing yourself a disservice for when a crash occurs.
That said, if you’re on a system where people don’t use the system much at all during off-peak hours… you can play around a bit with the start and end-dates – and it’s also worthwhile to try and make sure that your t-log backups aren’t happening when your full backups are occurring as well.
On more complex and heavily used systems, it’s not uncommon to spam transaction log backups to their own folders, but on smaller systems you can easily store them in the same locations as your full backups – which is what I’ll do here.
Likewise, if you’re backing up logs very frequently, you might want to retain fewer days worth of transaction log backups, but I personally like to keep at least two days worth – as you could, in a pinch, use a full backup from 2 days ago along with a long chain of t-log backups in the off-chance that you need to recover and find that your full backup from the previous night somehow became corrupted.
With those options configured, it’s time to start dealing with reporting options – where it’s possible to specify that you’d like a text file report each time your plan is run.
I find that that’s a great option when you’re troubleshooting problems, otherwise it’s typically something you won’t want to enable.
In most cases though, you’ll want to write the outcome, or history, of your maintenance plan’s execution to the msdb database, so I always leave this option checked.
And, as you can see there’s also an option to push these details up to a centralized server if you need or want.
Otherwise, after specifying those options we’re ready to complete the creation of our maintenance plan – though at this stage I’d really urge you to give your maintenance plan names some thought.

Especially if you’re going to have multiple plans (for different databases and for backups or maintenance tasks) on your server.

And the reason for this is that a good naming convention will be very helpful when it comes to troubleshooting failures and so on – because the last thing you want is to check on this jobs screen and see a red x, indicating failure, and then have to ‘tease apart’ which part of your maintenance plan failed.

Therefore, just about every time that I create a new maintenance plan, I then go in and manually rename the SQLServerAgent jobs that were created to execute the actual components of that plan – to make management and troubleshooting that much easier.

Otherwise, at this point, you now know how to create maintenance plans, and if you need to change any of the details you can just right click on them from the maintenance plans node – where making changes is as easy as walking back through the wizard and specifying the changes you want made.

And, even better, by walking through this wizard to create backup plans, you’re creating scheduled jobs, or tasks, that will fire off as directed and help facilitate one of the biggest components of your disaster recovery plan.

And with that, we’re done with this video, and we hope to see you in subsequent videos where we look at troubleshooting maintenance plans and backups as well as where we look at restoring and recovering from these backups.