Create MS SQL Backup Maintenance Plan

Probably one of the worst things that can happen is to loose data especially a database which is dynamically changed. In this case if you do not have backup, you could loose money, time, nerves, etc.

With few step-by-step tutorials I will show you how to set backup maintenance plan in MS SQL.

I will use Microsoft SQL Server Management Studio, where in the management section I will set new maintenance plan which will create MS SQL backups on daily basis.

First open MS SQL Server Management Studio and expand the ‘Management’ section, then ‘Maintenance Plans’ section:

Create MS SQL Backup Maintenance Plan

Right-click and choose ‘New Maintenance Plan‘

Create MS SQL Backup Maintenance Plan

Put a preferred Maintenance Plan Name :

Create MS SQL Backup Maintenance Plan

The next step is to setup the Job Schedule Properties, where we will set the ‘Schedule Type’, ‘Frequency’, ‘Duration’, etc. I have marked it with red squire on the next image.

Create MS SQL Backup Maintenance Plan

Put a name of the schedule choose the parameters that fits your needs, like once a day, once a week, and if you want the MS SQL backup plan to runs forever all the time do not forget to choose ‘No end data’.

Now comes the important part!

Before back up a database it is rather a must to check its integrity, so I will add ‘Check Database Integrity Task‘.
Just drag-and-drop it into the field in right, then right-click on it and choose ‘Edit’ to set certain options.

Create MS SQL Backup Maintenance Plan

In my case the connection is local so I will leave the first option ‘Connection:’ unchanged. If your MS SQL Server is on a remote machine, click on the field and set the remote machine name.

The next option is to select databases for backup. The options are:

All databases – this will backup every database on the server.

System databases – master, model, msdb, tempdb – these are in need of the MS SQL Server itself.

All user databases – this will backup the databases on the server excluding the system ones.

These databases – an option to choose separate databases for backup.

I will select ‘All user databases’ as this what I need.

Create MS SQL Backup Maintenance Plan

Once you are ready with this option, we will continue with MS SQL Back Up Database Task


Filed Under: MSSQL

Anthony Gee About the Author: Anthony G. is an IT specialist with more than 9 years of solid working experience in the Web Hosting industry. Currently works as server support administrator, involved in consultative discussions about Web Hosting and server administration. One of the first writers in the website, now writing for Free Tutorials community - he is publishing tutorials and articles for the wide public, as well as specific technical solutions.

Leave a Reply