IntroductionIn 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.
Use the following procedure to do a backup of a database. Step 1Log in to SQL Server 2008 with a correct user name and password.Step 2Start SQL Server Agent by right-clicking it and selecting Start (if it's not started already).Step 3Then expand the Management tab and right-click Maintenance Plans.Write the Proper name of your plan and click on the OK button.Step 4After 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 5Click the Calendar button on the top of that window then it will show the following window. Step 6Set the time frequency scheduling type depending on your requirements.Note: The scheduling type dropdown will show the following items. Select whatever is needed. Step 7In the Toolbox select Back Up Database Task (drag or drop it or double-click it).Step 8Now 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 9Now 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 1From the toolbar, drag and drop maintenance clean-up task as shown in the following image.Step 2Right-click on it and select Edit.Step 3Now 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. SummaryIn 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.
You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.
Become a sharper developer and jumpstart your career.
$0
$
. 00
monthly
For Basic members:
$20
For Premium members: