Create Backup (.BAK) File Of SQL Database Using SQL Server Job Agent

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   
  5.    
  6.    
  7.  -- specify database backup directory   
  8.  SET @path = 'C:\Backup\'   
  9.    
  10.    
  11.  -- specify filename format   
  12.  SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)   
  13.    
  14.    
  15.  DECLARE db_cursor CURSOR FOR   
  16.  SELECT name   
  17.  FROM master.dbo.sysdatabases   
  18.  WHERE name IN ('TestDB')   
  19.    
  20.  OPEN db_cursor   
  21.  FETCH NEXT FROM db_cursor INTO @name   
  22.    
  23.    
  24.  WHILE @@FETCH_STATUS = 0   
  25.  BEGIN   
  26.  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'   
  27.  BACKUP DATABASE @name TO DISK = @fileName   
  28.    
  29.  FETCH NEXT FROM db_cursor INTO @name   
  30.  END   
  31.    
  32.    
  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