Automated Backup Using SQL Server 2008

Introduction

In this article I am explaining how to create an automatic backup in SQL Server 2008 and every week delete the backup and store a new backup.

Here in this example I am storing a backup from Monday to Sunday and when the next Monday arrives the previous Monday's backup will be deleted and a new backup file is stored.

It is very important to take backups of database files on a regular interval. Microsoft SQL Server 2008 has provided us very easy tools to do it.

• One can take the backup to be taken at a regular interval.
• One can delete the backup copies at a regular interval.

Use the following procedure to do a backup of a database.

Step 1

Log in to SQL Server 2008 with a correct user name and password.

Step 2

Start SQL Server Agent by right-clicking it and selecting Start (if it's not started already).



Step 3

Then expand the Management tab and right-click Maintenance Plans.



Write the Proper name of your plan and click on the OK button.

Step 4

After clicking the OK button it will show the following window.



Give some discretion about your maintenance plan.

Note: If the Tool Box window does not appear then go to View and click on the Tool Box.

Step 5

Click the Calendar button on the top of that window then it will show the following window.



Step 6

Set the time frequency scheduling type depending on your requirements.

Note: The scheduling type dropdown will show the following items. Select whatever is needed.



Step 7

In the Toolbox select Back Up Database Task (drag or drop it or double-click it).



Step 8

Now click on the database image then the following window will be shown.



Set the Backup type; in this case it's full. Select the database for which you want to take the backup. Click OK.



Provide the folder name where you want to store the backup of your database. Here if you click on view T-SQL then it will show the SQL query generated automatically for your task.



Step 9

Now click on the OK button.

Now the Backup plan configurations is down and an automatic backup has been stored in your folder daily.

Note: The name of the file will be created by appending the date so that you can identify the back up for a specific date.

Now delete the backup file every seven days and store a new one there.

For this you clean up the task along with the maintenance plan. Use the following procedure and configure the clean-up task.

Step 1

From the toolbar, drag and drop maintenance clean-up task as shown in the following image.



Step 2

Right-click on it and select Edit.



Step 3

Now in the folder drop down select the folder where you are storing your daily backup then provide the file extension (backup file has a bak extension). Set the proper interval after which the backup file will be deleted. In this case it's one week.



Click OK; the task is then finished. Now the backup will be deleted after seven days and a new file will be created.



Summary

In this illustration you learned about taking an automatic backup and after a week delete the seven day's of prior data. Please provide your valuable comments about this article.