In this article, I am going to describe, how to create a SQL Server Agent job step, which executes SQL scripts in SQL Server 2012, using SQL Server Management Studio. I am giving you a example to take backup (.BAK) file of SQL Server database on a daily basis at some specific time, which is given in the job scheduler.

SQL Server Management Studio can be used to create a database backup job to take backup of a user database. Just follow some steps and UI work processes to create a simple backup job, run the job and you can see the results on the screen.

Step 1- SQL Server Management Studio and login with your credentials.

Step 2- After connecting to SQL Server, Expand Object Explorer=>SQL Server agent=>Expand Job=>Right click=>select in menu "New job". See the image, given below-

New job

Step 3- Select General=>type name of SQL agent job. You can write the description also. See the image, given below-

General

Step 4- Select Steps=>Click "New"=>"OK" on bottom of the screen. See the image, given below-

New

After clicking new, open new tab and see in next screen.

Step 5- Select General=>type Step name=>Command. In command panel, you can use SQL query. See the image, given below-

General

Here, my query is used to take backup of SQL database. I have created a cursor to take backup files of the database.

  1. Copy-paste code here to remove the line numbers.1. DECLARE @name VARCHAR(50) -- database name
  2. DECLARE @path VARCHAR(256) -- path for backup files
  3. DECLARE @fileName VARCHAR(256) -- filename for backup
  4. DECLARE @fileDate VARCHAR(20) -- used for file name
  7. -- specify database backup directory
  8. SET @path = 'C:\Backup\'
  11. -- specify filename format
  12. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
  15. DECLARE db_cursor CURSOR FOR
  16. SELECT name
  17. FROM master.dbo.sysdatabases
  18. WHERE name IN ('TestDB')
  20. OPEN db_cursor
  21. FETCH NEXT FROM db_cursor INTO @name
  24. WHILE @@FETCH_STATUS = 0
  25. BEGIN
  26. SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  27. BACKUP DATABASE @name TO DISK = @fileName
  29. FETCH NEXT FROM db_cursor INTO @name
  30. END
  33. CLOSE db_cursor
  34. DEALLOCATE db_cursor
Step 6- After creating Job, you can see in the panel, given below-

Job

Step 7- Now, you can see in jobs menu, your job has been created and is ready to start. Afterwards, you can start your job. See the image, given below-

start

Step 8- After clicking "Start job at step", your job is running and performing, according to your query. See the image, given below-

Start job at step

Also, you can check the status of the job in this panel.

Step 9- After job completion, you can see the "success" status for this job. See the image, given below-

success

Afterwards, you can check your back file (.BAK) file, which has been created on the specified path.

Step 10- After job completion, you can see the history of the job. See the image, given below-

history

Afterwards, you can see whole information and history of your job. See the image, given below-

history