How to Create and Back Up a SQL Server Database Part 2

Share on FacebookTweet about this on TwitterShare on LinkedIn

Share on FacebookTweet about this on TwitterShare on LinkedIn

How to create and Back up a SQL Server database Part 2

When supporting database driven applications, we can often run into issues. When database issues come up, it is a best practice to be able to recover a database and the data up to a certain point. In part 2 of my SQL Server blog, I will be explaining how to create a backup maintenance plan that will run on a daily basis and how to restore the database.

Creating a Backup Maintenance Plan

To set up a Maintenance Plan for the database backups, open the Microsoft SQL Server Management Studio and go to “Management” in the left pane Object Explorer tab, then select the “Maintenance Plan Wizard” as shown below.

How to create and Back up a SQL Server database Part 2 image 1

In the next window, click “Next”. When the following window appears, give your maintenance plan a name, for this example I will name it “My Backup Plan”, then click “Next”.

How to create and Back up a SQL Server database Part 2 image 2

In the next window, I will check on the “Backup Database Full” option and click “Next”.

How to create and Back up a SQL Server database Part 2 image 3

Since I only have one task for this example, I will click “Next” again at the next window. In the “Define Back Up Database (Full) Task” window, select the desired database you wish to back up and click “OK”. The rest of the options can remain the same so click “Next”.

How to create and Back up a SQL Server database Part 2 image 4

Click “Next” again at the following screen.

How to create and Back up a SQL Server database Part 2 image 5

Review your plan on the last screen. If you are satisfied with the tasks, click “Finish” and the plan will be created.

How to create and Back up a SQL Server database Part 2 image 6

Once the plan is created, you can right-click it, select “Modify” and set up the schedule.

How to create and Back up a SQL Server database Part 2 image 7

Click on the “Calendar” icon to set up the schedule.

How to create and Back up a SQL Server database Part 2 image 8

How to create and Back up a SQL Server database Part 2 Image 9

I modified the above window, selected “Daily” and a time of 5:00 am. Click “OK” to save changes.

How to create and Back up a SQL Server database Part 2 image 10

My back up plan is now set up. It will create a full database backup of the database “a_test_database” every day at 5:00 am.

Database Restore

For this example, I will delete the database I created earlier, then I will restore the database using the database backup file. You can also rename the existing database before you restore it. to be on the safe side but beware of disk storage constraints if restoring a large database. To delete the database, (**note: this is a dangerous task, make sure you really do not need the database and have a full back up before deleting it),right-click the database name and select “Delete” from the menu.

If you chose to delete the database first, you will need to right-click on the “Databases” folder and select “Restore Database…”.

How to create and Back up a SQL Server database Part 2 Image 11

If there is an issue with the database, or you would like to restore the database to the previous full backup – while it still exists-you can right-click the database name, select “Tasks”, “Restore”, and then “Database…” as shown in the screenshot below.

How to create and Back up a SQL Server database Part 2 Image 12

The rest of the steps will apply for either of the two options you selected above, either restoring a deleted database or restoring an existing database.

To restore the database from the backup file you have created, click on the “Device:” radio button, and then on the “…” button to browse to the backup file name location as shown below.

How to create and Back up a SQL Server database Part 2 Image 13

On the following window below, click the “Add” button and select the file you wish to restore from, then click “OK”.

How to create and Back up a SQL Server database Part 2 Image 14

After selecting the file and clicking “OK”, you will get the following window. Verify the file that will be used for the restore and click “OK” again.

How to create and Back up a SQL Server database Part 2 Image 15

The next screen will show the “Restore Database” window. Click “OK” to begin to restore the database. After the database restore is completed, you will see the following message.

How to create and Back up a SQL Server database Part 2 Image 16

Once the entire process is complete, open the database and query the table contents. The data will be displayed showing that the database was restored successfully.

When managing and maintaining a database, it is important to have a backup procedure in place. For best practices, backing up a database on a nightly basis is a good idea. If you ever need to restore a database, you can use the previous full back up and possibly only risk losing some of the data updates instead of everything. These steps are based on SQL Server 2012 but this process has remained similar in other versions of SQL Server.