Work Instruction for Full Database Backup

  1. Please login to the SQL Management Studio using local SA Account.

  1. After login, proceed to the Management Menu and expand it. From the Menu select Maintenance plan

  1. Right click on the maintenance plan and choose Maintenence Plan Wizard.

  1. Procced with wizard and Name it as Full Database Plan and then click on change button to make changes on JOB Schedule

.

  1. Change the Schedule as per the given screen shot below and the apply the changes.

  1. In the Select Maintenace tasks choose the following.
    1. Check Database Integrity
    2. Update Statistic
    3. Cleanup History
    4. Backup Database (FULL)

  1. In the Select Maintenace Task Orderdon not change the order. We can leave it as it is.

  1. In the Define Database Check Integrity Task, click on the database drop down and choose all USER DATABASES (excluding master, model, msdb tempdb)

  1. In the Define Update Statistic Plan, click on the dropdown of database and choose choose all USER DATABASES (excluding master, model, msdb tempdb)

  1. In the Define Hostory Cleanup Task let all the settings to be a default. Sine we are not dealing with user databases we can only keep database hostory for 4 weeks.

  1. In the Define Backup Database (Full) Task click on the dropdown of database and choose choose all USER DATABASES (excluding master, model, msdb, Temp DB)

    Also we need to change the Path of the backup file to E:\Database Backup\Databases along with need to select Verify Database Integrity. In the SetBack up compression we need to choose Compress Backup.

  • In the next step, Select Report Options choose E:\Database Backup\Databases as folder location and procced to the final step

 

  1. In the final step you will be presented with all the configuration which we have selcted tell now. You can verify them once and then hit FINISH Button.

  1. Once the setup is finsiged we will be presented with the following screen.

This will complete the Maintenace Wizard Task. We can verify if the plan has been created by the following method.

  1. If we navigate to SQL Server agent and expand the Job Section we will see a sub plan for the maintenance task which we have created.


Leave a Reply