MS SQL Backup 2012 Backup Process

How to Get SQL Backup?

We will explain how to do SQL Backup (database backup) with Microsoft SQL 2012 step by step with pictures. First of all, open the SQL Management Studio program and log in with the sa (administrator) user.

SQL Server Maintenance & Plan Wizard

In Object Explorer, right click on Maintenance Plans(1) in the Management tab.

Specify SQL Backup Plan Name

In the screen that opens, type the name of the plan in the Name section. In the Run As section, select the service on which the backup plan will run. Since we will make backups at certain intervals, select the“SQL Server Agent service account” option and press the(2) Change button to schedule.

Then let’s proceed with the Next button on the screen that opens.

SQL Backup Task Scheduling and Frequency Setting

In this screen, Recurring is selected as Schedule type and Enable is checked. Then, how often the backup will be performed is set in the Frequency section. Choose between Daily, Weekly and Monthly options. For example, you can customize it as“every 3 days“, “once a month”, “once every 3 weeks”.

Ideally, the backup is taken on a daily basis. Selecting this option will open the Daily Frequency section. Here you set the time at which the backup will run with Occurs once at. If you want to back up repeatedly throughout the day, which I recommend, then select Occurs every. Choose how many hours apart. For example, here I have asked for 4 hourly intervals. This means that the backup will run 6 times a day.

Set the start and end times as shown in the picture. Duration helps you to set the time from which the plan you have written should run. If you leave it untouched, it will start immediately from today. If you want it to run until a certain date, you can specify this in the End date section.

SQL Full Backup Option

On the next screen, select Back Up Database (Full) in the options and proceed.

If more than one task is selected, you can prioritize them on this screen.

Since we are not selecting more than one task on the screen that opens, we will not do any sorting, so we say next.

Export SQL Backup

In the next step, click on Database. You will be asked which database you want to backup. You can choose as in the picture or you can say that the whole database should be taken.

Then press the add button. Here we will specify the file name and path as in the picture. The important point here is, definitely do not forget to write the extension with “.bak” at the end of the file name. Select Overwrite or Append. Proceed to the next step by saying Next.

SQL Backup Result Report Sending via Mail

SQL Backup Plan Summary

Subsequent screens will show plan summaries and screens indicating successful installation.

By opening Job Activity Monitor, you can right click on the plan you have prepared from there and run it manually once with Start Job at Step.