Automation refers to scheduling the regular maintenance tasks. Whenever schedule time comes automatically that task will be executed. DBA regular activities can be automated we can get the status f the task in the form of alert and helpful while generating reports.
Automation can be done in 2 ways,
Sql server agent jobs useful to perform daily scheduled maintenance tasks.
Right click on the job folder under sql server agent → Select New job from options menu
General → it helps DBA’s to provide identification of jobs
Name → we need to provide name for the job
Owner → always job owner is to be sysadmin(SA)
Category → under which category this job falls, mostly all DBA tasks falls under database maintenance
Decryption → provide some description about the job
Enabled → job will be created now, we can enable later using this option.
Steps: Steps are nothing but a task under job
Select New button
Step name → PROVIDES DETAILS OF STEP OF JOB
Type → sql server uses T-sql language by default
Runs → Only that privileged persons can able to run this job. If we specify
Database → By default it points to master, we can change as per requirement
Command → T-sql command to perform the task
Parse → we can verify syntax errors of the T-sql command using this option.
On success → provide next steps once the first step complets means it has go to next step (or) it has to quit the job.
Retry attempts → no.of retry attempts need to specify if a job fails it will attempt try for specific times and report to user if fails again.
Retry Internal → wait for specified time and attempt next try
On failure → on failure where this job have to go whether it has to start from beginning (or) quit the job reporting failure to user.
Output file → we configure output file to get the information about the job. If it fails with what error it fails we will get from this.
Append output → overwrites the existing job information
Log to table → if we want job running status data in the form of table.
Output in history → to view history of job execution
We can add, edit, delete steps more the order of steps using insert, edit and delete buttons.
Name → name of schedule we need to provide here
Schedule type → whether it is one time job (or) daily job we need to provide type.
Enabled → schedules it to be enable now (or) later
Occurs → whether it is daily (or) weekly (or) Monthly
Recurs every → it will re-run the stops on specified day.
Occurs every → we need to specify the frequency of executing this job means every 1 hour (or) 4 hours as per our requirement
Summary of job will be displayed at the end.
Once all the details provide click OK, it will create a job with specified name.
Right click on job → select script job as → open job in new query window → copy the script of job place on different server and execute<strong>.</strong>