Create sql server database backup(.BAK) file using sql server job agent.

In This Article i am going to describes how to create a SQL Server Agent job step that executes SQL scripts in SQL Server 2012 by using SQL Server Management Studio,I am giving you a example to take backup (.BAK) file of sql server database on daily basis at some specific time which is given in job scheduler.

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

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

Step 2:- After connect to sql server,Expand object explorer=>sql server agent=>Exapnd Job=>Right click=>select in menu "New job".see image..

Step 3:- Select General=>type name of sql agent job,you can write description also. see image....

 Step 4:- Select Steps=>Click "New"=>"OK" on bottom of screen.see image....

After click on new open new tab see in next screen. 

Step 5:- Select General=>type Step name=>Command ,In command panel you can use sql query.see image...

Here my query is use to take backup of sql database.I have created a cursor to take backup file of database.

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 created Job you can see in this panel.see image...

 Step 7:- Now you can see in jobs menu your job has been created and ready to start,after that you can start your job.see image.......

Step 8:- After click on "Start job at step" your job is running and performing according your query.see image.... 

Also you can check status of job in this panel.

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

 After that you can check your back file (.BAK) file has been created on specified path.

 Step 10:- After job completion you can see the history of job.see image..

After that you can see whole information and history of your job.see image..

You have just read an article that categorized by title SQL by title Create sql server database backup(.BAK) file using sql server job agent.. You can bookmark this page with a URL https://bikeshsrivastava.blogspot.com/2016/09/part-33create-sql-server-database.html. Thank You!

Author: Bikesh Srivastava - Friday, September 2, 2016