How to create a backup Maintenance Plan in SQL Server
Articles,  Blog

How to create a backup Maintenance Plan in SQL Server


In this demo, I’m going to show you how to
create a basic backup Maintenance Plan. While Maintenance Plans may not offer the best solution
for your environment, particularly if you manage many servers, it does offer a quick
and easy way to get started with a backup solution. I’m going to create a plan that
contains steps to do full, differential, and transaction log backups. I won’t go through
scheduling the three different parts of it: I’ll leave that up to you. We’ll just create
the plan and you can do this in your environment and schedule the steps later on. What I’m
going to do is expand the Management node in the Object Explorer, I’m going to right-click
on Maintenance Plans, and click New Maintenance Plan. I’ll call this “Database Backups,” and
we’re presented with a design surface here where we can drag-and-drop our backup database
task. What I’m going to do first, though, is create three different subplans and these
are going to be for our full, differential, and transaction log backup plans. Let me just
edit this, and get these set up. So click Add Subplan to create another one. I don’t
like that they automatically populate the description. Okay, so now I’ve got three subplans,
and I’m going to switch back to the Full, and I’m going to drag-and-drop a Backup Database
Task onto the design surface. I’m going to right-click, and click Edit, and now we’re
going to go through and configure the task. This one is going to run on the local server,
our backup type is Full, and what I’m going to do is click the dropdown arrow there and
say I want to backup all the databases — it just so happens that this instance is completely
empty right now — I’m going to check the “Ignore databases where the state is not online,”
which will skip databases where it’s not possible to back them up. If this was unchecked and
you had databases that were offline, for example, when you ran the Maintenance Plan, you would
get errors. So I like to check this. The next thing is I’m going to click “Create a sub-directory
for each database.” What this is going to do is it’s going to create a folder beneath
this backup folder for each database and put all the backups for that database in that
folder. This is very handy, particularly if you are doing frequent transaction log backups
because the number of files that you end up with during your retention period, which is
usually — well, I shouldn’t say for your environment — but probably at least two weeks,
times at least every hour, adds up to a lot of files, particularly if you have many databases
in your instance. So I really like this option. I’m also going to click “Verify backup integrity”
— this will do a RESTORE VERIFYONLY, just to do a quick check to make sure the backup
is okay. This is not a full-blown restore test, but it’s good to enable this because
it’s quick and it’s pretty much the best you can do just using a Maintenance Plan. In terms
of backup compression, I’m going to leave it to use the server default setting because
when I configure my servers, I will set backup compression on by default on the server. So
we don’t really need to set this away from just using the server default, unless you
have a specific scenario that either you don’t want it on purpose, or you do want it on purpose.
So that’s good, and we’ll click OK. The Full database backups are ready to go, now I’m
going to switch and do the same thing with the Differential backups. I’m going to click
on the Differential subplan, and you can see the design surface has cleared now to reflect
that, so we’ll drag-and-drop another Backup Database Task, right-click and click Edit.
This time we’ll select the differential backup type, same thing, we’ll select all databases,
and ignore where it’s not online, click OK, and the same thing, same thing, and click
OK. Very simple. Now we’ll do the Transaction Log. I’ll select transaction log here, and
you’ll note that it tells you that databases in SIMPLE recovery will be automatically excluded,
so you don’t have to worry about it causing errors. You can select all databases, and
it will automatically exclude those. Great, this is actually all there is to it. All you
have to do now is — I’m going to save this off, and say Yes, and now we have our Maintenance
Plan here, and what we have to do is actually go in and set schedules for the jobs it’s
created. So you can see it has created three jobs, one for each of our subplans, but if
I right-click and go into Properties, and I go into Schedules, you can see that they’re
unscheduled. So I’ll leave that up to you to create schedules for these, but that’s
all there is to it, and thanks for watching!

20 Comments

  • Olivier Sipanko

    Fantastic video!
    Could you please brush up on how to schedule a backup maintenance plan?

    Kind Regards

  • Mohammed Ahmed

    Please you can help me, why when I install SQL server 2012 not appear <<SQL Server Agent>>in SQL Managment Studio.
    While I installing all this files
    1-SQL Managment Studio – Complete
    2- SSDTBI_VS2012_x64_ENU
    3- SQLEXPR_x64_ENU
    4- SQLEXPRADV_x64_ENU
    5- SQLEXPRWT_x64_ENU
    6- SqlLocalDB

    Thanx for all you cooperation

  • Md.Rayhan Akond

    pls sir share me you sql sever 2008 R2 full version software
    I hope there I will get Maintenance Plan folder …. pls pls pls pls pls pls pls

  • Chris Nelson

    In terms of purely backing up databases, how would you say this method performs in relation to setting it up through SSIS, My business has SSIS but they use these style of maintenance plans instead and I am wondering what the pros and cons are of each

  • Megha Misra

    Can you please tell the difference between Full, Differential and Transaction Log? and if I'm taking a backup how can I save old and new backup ( will it overwrite each time)?

  • Bando Bando

    Hi, Mantainance Plan offers a lot more options such as Check database Integrity, Shrink Database, Reorganize index and so on. I would like to know the best order to run this tasks, for example should I Reorganize before or after backup or shuld I rebuild Index if I don't reorganize? Or should I do this things at all? I'm having problems with a 2GB database that has a lot of use and get slow and sometimes generate deadlocks. It fixes for a while when I run a script that shrinks the database/transaction log. I was reading that a backup even as frequent as 15 minutes can be a real solution. I'm now in full recovery mode. Sorry for the long question.

  • Brian Lockwood

    Here is a good article on automating backups with maintenance plans Multiple methods for scheduling a SQL Server backup automatically https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/

Leave a Reply

Your email address will not be published. Required fields are marked *