Using SQL Server 2012 Agent to create a Database backup SQL job
If you are a SQL server DBA at your company, scheduling jobs and automating Admin tasks is a common practice in your daily life. Of course as the number of SQL servers and the underlying databases grow, this vital role for a SQL DBA can quickly get out of hand. This is where SQL Server 2012 Agent can come to your rescue.
SQL Agent is a separate service in SQL server 2012 DBMS. It has the ability to setup and schedule automated jobs that can run on a predefined schedule. This can include DBA things like backing up your databases on a regular intervals, running import and export jobs, executing a predefined Transact SQL scripts and even deploying PowerShell commands.
Let’s go ahead and dig deeper into some of these components
A visual representation of the MS SQL Agent is as follows.
Create a backup job for Northwind database
We are going to create a backup job for Northwind database using SQL Agent. First make sure the Agent service is running, if not right click on SQL Server Agent and choose Start. Next do this:
Jobs > New Job
You will get a new dialog box. Enter the following info for job:
Name: Backup Northwind
Category: Database Maintenance
Next go to Steps in the left pane. Then click on New in the middle pane. Basically you are creating a job step that will run the backup command for your database.
For Step name type “Run SQL”
Next go ahead and enter the following SQL code:
BACKUP DATABASE [Northwind] TO DISK = N'C:\SQLBackups\Nwind.bak' WITH NOFORMAT, INIT, NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
You always want to parse the sql code and test it. When we ran out SQL command, we got the following output which means that it WORKS!
11 percent processed. 21 percent processed. 31 percent processed. 41 percent processed. 51 percent processed. 61 percent processed. 71 percent processed. 81 percent processed. 91 percent processed. Processed 560 pages for database 'Northwind', file 'Northwind' on file 1. 100 percent processed. Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1. BACKUP DATABASE successfully processed 562 pages in 0.410 seconds (10.690 MB/sec).
Here is what the new job step looks like in our SQL Server 2012 Agent instance.
Generally you should enable logging which means track execution of your SQL jobs.
Click on Advanced in the left pane
Go ahead Check Log to table and make sure to select Append output to existing entry in table
This basically will provide forensics (big word for SQL geeks) on job executions as time goes on. Btw you can also write this to a file on a shared drive. I actually prefer that option over the Logging table. Click Ok. We can certainly add alerts and a recurring schedule to this. For now lets go ahead and click OK to finally create the sql job in SQL Server 2012 Agent.
Now we are ready for the million dollar question, is this going to work??
Let’s find out by manually executing this job. Under Jobs folder find Backup Northwind. Right click on it and select
Start job at Step
Since we have only one step, the job should start doing its magic. If you did everything correctly, you should get this nice dialog box.
Of course you never assuming anything as a SQL Server DBA and always verify the results. We are going to browse to the folder location and look for the backup file. Here we go:
Yep its there, BINGO!
Related topics to SQL Server 2012 Agent
Other important things SQL Server 2012 Agent can do are the following:
Operators: An operator in SQL server is someone who can be notified or alerted when something goes wrong in SQL server. And it can also be used as FYI information when an important SQL job completes.
Alerts: Alerts are notifications. These are customized SQL alerts based on error messages or Severity levels. Think off an alert as a heads-up which can then in turn execute a job or notify an operator.
Proxies: SQL Server 2012 Agent proxies are specific accounts with particular permission so that you can have fine-tune control on your SQL server. This will minimize any related security risks.
Error logs: This is the placeholder for any execution related info that is tied to a SQL server job or its underlying job steps. Basically this keeps a chronological order of all the executions with different messages that get generated as the job is executing. Def helpful for troubleshooting purposes