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.

 

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

 

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

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

 

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

 

 

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

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

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.

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

9. Choose the backup destination

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.

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.

• Plan is in progress

 

12.Maintenance plan wizard is successfully created.Click Finish