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.
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”.
In the next window, I will check on the “Backup Database Full” option and click “Next”.
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”.
Click “Next” again at the following screen.
Review your plan on the last screen. If you are satisfied with the tasks, click “Finish” and the plan will be created.
Once the plan is created, you can right-click it, select “Modify” and set up the schedule.
Click on the “Calendar” icon to set up the schedule.
I modified the above window, selected “Daily” and a time of 5:00 am. Click “OK” to save changes.
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.
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…”.
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.
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.
On the following window below, click the “Add” button and select the file you wish to restore from, then click “OK”.
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.
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.
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.