SQL 2014 - Creating a Database Maintenance Plan
Launch the SQL Server Management Studio from the start menu.
Login to the SQL Server as the SA User.
The Database we will be working with VSDatabase, we can scroll down and click Server Objects then click Backup Devices.
Right click Backup Devices and choose New Backup Device.
Enter in the Backup Device name we typically names these after the database. Since we will be creating two devices lets use VSDatabase_a.
The file location defaults to the instance installation under the BACKUP folder.
Repeat the process for VSDatabase_b.
Lets scroll down to Maintenance Plans, right click this folder and choose New Maintenance Plan Wizard.
The default screen appears and you can select Next.
We will give the first plan the name of INT-OPT short for Integrity and Optimization.
This plan typically runs on Sunday around 10:00 PM. So adjust the schedule accordingly.
Proceed to the next step by pressing OK.
Click Next to continue the Wizard.
This job will do the following
Check Database Integrity
Reorganize Index
Rebuild Index
Update Statistics
Place check marks next to those items and continue.
The order should stay default so just press next.
In the Database drop down menu select the database or databases this job should apply to.
Click Next.
In the Database drop down menu select the database or databases this job should apply to.
Click Next.
In the Database drop down menu select the database or databases this job should apply to.
Click Next.
In the Database drop down menu select the database or databases this job should apply to.
Click Next.
Yes we want the report to be generated, Click Next to continue.
Finally Click Finish and it creates the Job.
If the Job is successful you can click close.
You can now see the job under the Maintenance Plans folder.
We will now create a backup job for the database. From the menu on the left right click Maintenance Plans and choose New Maintenance Plan wizard.
This is the first backup job so we will name it VSDatabase_a.
Click Change to setup the schedule.
Change the Occurs drops down to Daily
Recurs every should be 2 days.
Occurs once at 11:00 PM
Start Date is today
Click OK to continue
Click Next to proceed
Select Back Up Database (full).
Click Next to verify.
From the Drop Down next to Database(s) choose the VSDatabase to backup and then clock OK.
On the destination tab at the top click the Back up databases across on or more files radio box.
Click Add.
Click the Backup device radio button and from the Drop Down select VSDatabase_a. This is the backup device you setup previously.
Click OK.
From the If backup file exists drop down choose - Overwrite.
Click Next to continue.
Leave the option to write the log file and click Next to continue.
Finally press Finish and the job will be created.
If there were no problems you can close the job.
Now we start the process over again with the second backup job.
From the left side right click Maintenance Plans and choose New Maintenance Plan Wizard.
Give this job a name in this case VSDatabase_b.
Click Change to edit the schedule.
Because the jobs alternate in this job you will want to set options like this
Change the Occurs drops down to Daily
Recurs every should be 2 days.
Occurs once at 11:00 PM
Start Date is tomorrow
Click OK to continue
Click Next to proceed
Select Back Up Database (full).
Click Next to verify.
From the Drop Down next to Database(s) choose the VSDatabase to backup and then clock OK.
On the destination tab at the top click the Back up databases across on or more files radio box.
Click Add.
Click the Backup device radio button and from the Drop Down select VSDatabase_b. This is the backup device you setup previously.
Click OK.
From the If backup file exists drop down choose - Overwrite.
Click Next to continue.
Leave the option to write the log file and click Next to continue.
Finally press Finish and the job will be created.
If there were no problems you can close the job.
To run the jobs just select them from the right and click Execute.
The job progress windows will open and let you know when its complete.
When the job is done just press close and run the other jobs.
No comments:
Post a Comment