How to create backups using database maintenance plan

You can create a database maintenance plan to automate the SQL Server database backups. SQL Server backup maintenance plan can be scheduled to backup the databases automatically or executed manually.

Follow the steps below to create a database backup maintenance plan and schedule it to execute automatically

1.Open SQL Server Management Studio, expand the Management node, and then expand the Management Plans node.

 

image

2.Right-click Maintenance Plans, click Maintenance Plan Wizard.

image 

3.SQL Server Maintenance Plan Wizard window will appear.click next.

image

4.Then type  a name for this database backup plan.

image

 

  • Select schedule according to your need.Generally daily,weekly,monthly or hourly.

 

image

 

5.Select  the maintenance tasks,which you wanted to plan.

image

6.select the order of your plan.Click Next.

image

7.On the Define Back Up Database (Full) Task dialog box, specify information about the full backup. Specify database where this plan has to be applied.Press ok.Then Click Next.

image

image

8.On the Define Back Up Database (Transaction Log) Task dialog box, configure the transaction log backup. Click Next.

image

9. Choose the backup destination

image

10.On the Define Maintenance Cleanup Task dialog box, configure the cleanup tasks.specify the folder name where you take backups.Then specify the backup folders extension.Click Next.

image

11.On the Select Report Options dialog box, select whether to write the report to text file or send the report through email. Click Next.

image

  • Plan is in progress

image

 

image

12.Maintenance plan wizard is successfully created.Click Finish

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s