Cloud Lab-11: How to Maintain vCenter DB with SQL Maintenance Plan (Part 2)

Just for quick recall. in last post of SQL Maintenance plan part 1, we have configured some maintenance tasks such as rebuild index, full backup of user (vCenter) Database etc. In part 2 of this lab, i moved forward and show you the reset of activities.

LAB-11 TASKS (Part 2)

In this lab we will perform following tasks.

  1. Configure Differential Backup on daily basis
  2. Remove the back files after 1 week (Set Backup Retention)
  3. Test/verify the Scheduled Jobs

1-Configure Differential Backup on daily basis

To create a “Daily differentials” backup tasks for vCenter DB’s. Let’s right click on the existing plan and “modify it”

vcloudlab12-vcsqlmaintainence-19

Lets add a new “subplan” to make our daily backups for the user databases.

Click on vcloudlab12-vcsqlmaintainence-19.1

vcloudlab12-vcsqlmaintainence-20

Let’s enter a name and description > Click on “Calendar icon” to set the schedule.

vcloudlab12-vcsqlmaintainence-21

Set the backup frequency to “daily” and set the “backup time” -> Click “OK”

vcloudlab12-vcsqlmaintainence-22

Verify your settings -> Click “OK”

vcloudlab12-vcsqlmaintainence-23

now rename the default “Subplan_1”. Double click on it and rename it to a more explicit name.

vcloudlab12-vcsqlmaintainence-24

Now we will add the necessary tasks

Drag and drop  from the toolbox to blank pane . vcloudlab12-vcsqlmaintainence-24.1

vcloudlab12-vcsqlmaintainence-25

Right click on the added task and click “Edit”

vcloudlab12-vcsqlmaintainence-26

Choose the type -> Differential; the databases -> all user DBs; don’t forget the extension “bak_diff“; the click verify and the subdir checkbox -> Click “OK”

vcloudlab12-vcsqlmaintainence-27

2-Remove the back files after 1 week (Set Backup Retention)

Now we add another task ->Drag and Drop on the Main Pane.

vcloudlab12-vcsqlmaintainence-27.1

vcloudlab12-vcsqlmaintainence-28

Right Click on the Maintenance cleanup tasks -> Click “Edit”

vcloudlab12-vcsqlmaintainence-29

Set the retention of differentials back policy into “1 week”. Set other options as highlighted -> Click “OK”

vcloudlab12-vcsqlmaintainence-30

Now “link” the backup task to the maintenance one

vcloudlab12-vcsqlmaintainence-31

Let’s add again a vcloudlab12-vcsqlmaintainence-27.1

vcloudlab12-vcsqlmaintainence-32

“Edit” it.

vcloudlab12-vcsqlmaintainence-33

Now let’s select the type “Maintenance Plan Text Reports”, go to the output report folder. I personally use retention of “2 weeks”. because it gives a little history of what happened in case of troubleshooting.

vcloudlab12-vcsqlmaintainence-34

“Link it” to the previous tasks.

vcloudlab12-vcsqlmaintainence-35

All is set. “Save the plan”

vcloudlab12-vcsqlmaintainence-36

Go the “SQL Server Agent” -> “Jobs”; you will see the 2 jobs named after the subplan you have just added and renamed.

vcloudlab12-vcsqlmaintainence-37

3-Test/verify the Scheduled Jobs

Now let’s try these. Right click on Weekly Full Plan-> Click “Start Job at Step on the job”

Be careful to run the vcloudlab12-vcsqlmaintainence-37.1 First, It is on this one that the full backup is set and we want to have a full backup of the databases before running differential ones. Be also certain not to run this plan during the off peak hours of your vCenter. The plan will drain its performances when it runs the different tasks such as the index defrag.

vcloudlab12-vcsqlmaintainence-38

If you got following error. This error occurred because my user don’t have the rights to execute the plan.

vcloudlab12-vcsqlmaintainence-39

I googled it and found following solution. error occurred because my SQL user don’t have the ” job execution rights”. 

————————————————————————————————–

EXEC msdb.dbo.sp_update_job

@job_name=’User Databases-Maintenance Plan.Weekly-Full’,

@owner_login_name=’sa’

GO

————————————————————————————————–

Go to “New query” button. Copy and paste the script “change the job name” and click “Execute”. Verify is should be completed successfully.

vcloudlab12-vcsqlmaintainence-40

Let’s try again to run the job. You can see below. It is successful this time. Click “Close”

vcloudlab12-vcsqlmaintainence-41

If you go in the user databases repository you will see the new differential backup file

vcloudlab12-vcsqlmaintainence-42

Similarly run the same script against our second job.

————————————————————————————————–

EXEC msdb.dbo.sp_update_job

@job_name=’User Databases-Maintenance Plan.Daily-diff’,

@owner_login_name=’sa’

GO

————————————————————————————————–

Go to “New query” button. Copy and paste the script “change the job name” and click “Execute”. Verify is should be completed successfully.

vcloudlab12-vcsqlmaintainence-43

“Execute” the job. This time it should be successful -> Click “Close”

vcloudlab12-vcsqlmaintainence-44

Similarly, If you go in the user databases repository you will see the new differential backup file

vcloudlab12-vcsqlmaintainence-45

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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