This will walk you through setting up a FULL backup solution for ALL databases on a server and setup the cleanup task to delete older backup files. 

 

NOTES and Assumptions:

  • It is very important to make sure you calculate the size that your backups will consume on the disk and size your disk needs accordingly.  The wizard gives you the option to expire backups and remove them. 
  • This walk through assumes you have access to the SQL Server Windows machine via Windows Authentication. 
  • This example is for a FULL backup (think LEVEL ZERO in RMAN) ... You can in Step #7 choose differential backups. If you setup a Differential Backup(s) you can then apply PIT recovery via transactions logs assuming all files & logs are present during recovery.  But that is a different topic.

STEPS:

1). Log into box running SQL Server (i.e. SF-DUBLIN17).

 

2). Start SQL Server Studio, and choose Connect.

 

3). On the left side choose, Management.

4. Right click Maintenance Plans, and choose Maintenance Plan Wizard.


5. Give the new plan a NAME, Description.  Leave Run AS set to default SQL Server Agent service account.

 


6. Next at the bottom of this screen pick CHANGE on Schedule.  Use this screen to pick when backup occurs, and for how long.  In the example below the backup runs every night at midnight and will do so forever (until changed).

Click OK when ready.


Notice the schedule is now filled in.  Now click NEXT.
 

7. On the next screen choose Back Up Database (Full) and pick Maintenance Cleanup Task. 

(NOTE: you can choose multiple task and choose the order in which they are run).

  Now click NEXT.

 

8.On the next screen choose the order for tasks to be run.  Obviously we want the backup run first. 

(NOTE: Like I mentioned above you could also choose other tasks like Rebuild Index, Reorganize Index, Shrink Database, etc ...)

Now click NEXT.

 

9. On the next screen we will determine which databases to backup and where to back them up.

Choose Database(s) and pick All Databases then click OK.

  Now click on the Destination tab.

 

10. Use the ... button to choose where (what disk) to store the backup(s) on.  I suggest clicking the create sub-folders for each database. In this example we are backing up everything to the E: drive under E:\Backups and giving each file a .bak extension.

Now click on the Options tab.

 

11. Choose the expiration date of the backup by picking Backup Set Will Expire field.  In this example we will keep the backup for 14 days. 

(Note you can also choose compression, verifying the backup here.  I leave them set at default.)

Now click NEXT.

 

12. This screen sets up the backup file deletion plan.  Make sure you point it (middle of screen) to where you sent your backups (see Step #10), click the Include first-level subfolders box and choose the BAK file extension.  This example shows files older than 2 weeks will be deleted.

 Now click NEXT.

 

13. Determine where to write the backup report.  In this example we will write it to E:\Backups\Backup_Reports

Now click NEXT.

 

14. The last screen gives you a summary of your new plan. 

Now click Finish.

 

15.The plan will be validated and saved.

Now click Close.

That's it you're done. 

 

Now if you go back to the Maintenance Plan screen you will see your new plan in place!

By Double clicking your new plan you can see the layout.

If you need to make any changes to your plan you can double click the actual part of the plan and it will take you back to the creation task for changing/updating.

If you need to delete the plan simply right click the plan and choose DELETE.

 

 

  • No labels