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

In this article, you will learn simple steps to create backup files of SQL database, using SQL JOB.

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