Backup User Databases Using a Maintenance Plan
10. In the Select Report Options screen, select the checkbox Write a report to a text file and also provide the folder location where the report needs to be saved. Click Next to continue with the wizard.
11. In Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard, click Finish to complete the wizard.
12. In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.
13. Once the “Backup AdventureWorks database” is created successfully. The Full, Differential & Transactional Log backups related independent SQL Server Agent Jobs will be created and they will have the schedules as provided by you during the design of Maintenance Plan.
Database Administrators should use a combination of Full, Differential and Transactional Log backup options when they create maintenance plan for backing up user database. By using differential and transactional log backups a DBA can reduce the time taken to restore user databases when there is a disaster. However you should have proper backups of system and user databases, then only you will be able to restore and recover SQL Server in the event of system failure. In this article you have seen how to leverage Maintenance Plans to take Full, Differential and Transactional Log backup of the AdventureWorks database.