AUTOMATIC BACKUP IN SQL SERVER

This entry will explain the implementation of an automatic backup in a SQL Server database using the SQL Server Agent.

First, we need to connect to a 'Database Engine' using the 'sa' user or an administrative account in the Microsoft SQL Server Managment Studio.

Once the SQL Server Agent tree is opened click on the New Job option.

A new window is displayed and asks for the following fields:

• Name: The name of the job.
• Owner: The owner of the job.  A existant login in the database
• Category: Useful to group the database jobs.
• Description: Useful to document the job.
• Enabled: Checkbox that activate or deactivate the job after create it.

After filling all fields, click on the Steps Page.

Click on the New button to add a Script.

A new window is displayed and asks for the following fields:

• Step Name: It's possible to define multiple steps in a job, this is the name of the step.
• Type: The step permits to add different kind of Scripts (Operating System, ActiveX, Analysis Services, Integration Services Package) To automatic Backup choose Transact-SQL. 
• Run as: In case that you want to use impersonation.
• Database: Database where the script starts it's execution.
• Command: The script itself.

After filling all fields, click OK to return to Steps page and click again on the Schedule Page or add more steps in case that it's necessary.

Click on the New button to add a Schedule.

A new window is displayed and asks for the following fields:

• Name: It's possible to define multiple schedules in a job, this is the name of the schedule.
• Schedule Type: The Schedule permits to add four different ways: (At the start of the SQL Server Agent, when the CPU becomes idle, recurring or one time execution) To automatic Backup choose Recurring. 
• Frecuency: The schedule may occurs Daily, Weekly or Monthly. Each one has his own options.

After filling all fields, click OK to return to Schedule page.

Alerts and notifications can be set, or the Job can be created clicking again in the OK button.