Sometimes I receive a request for instructions on how to set up a SQL Server Agent Job to backup a database.
Setting up a SQL Server Agent Job is easy with the GUI. Follow these instructions to set up a basic SQL Server Agent Job.
Once you’ve set up this job to manage a backup , you can schedule a job or add custom error handling , for example, sending an email on success or failure or SQL Server - BackUp All Databases
Creating a backup can cause a high level of IO . Consider the impact of creating a backup on a server read more on BACKUPIO and sql database backup
Create a SQL Server Agent Job
Open SQL Server Management Studio (SSMS ) and connect to the SQL Server Instance. Expand the SQL Server Agent node and right click on Jobs , and click on New Job
Type the name
Type a name for the SQL Server Agent Job. Also make sure the owner is OK and has sufficient permissions
Click on Steps
Create the job step
Click on the New button
Add the code
Add a Step name and then add the code. The code in this example is to backup a database to disk . Use the script on SQL Server - BackUp All Databases to backup all databases. Customise the code for your requirements.
Click OK to create this job
Start the job
Expand the SQL Server Agent Job , right click on your job and click the Start Job option
Once you’ve started the job , a Process dialog will appear , reporting on in- progress job status
Check View History
Expand the Jobs node , right click on the Job and View History.