I have 4+ experience and working as software Engineer with Hytech professionals India PVT ltd and also working as Consultant,Trainer,Blogger and Author on Microsoft technologies,Also I have Trained more than 500+ IT professionals.I have knowledge about Asp.net,C#,SQL,Project management,replication,MVC,WebApi,SSRS,PostMan,TFS,SVN,Swagger,WMI,Agile,SignalR,Angularjs,IIS configuration.

Part 33::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 Part 33::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, 2 September 2016

2 comments to "Part 33::Create sql server database backup(.BAK) file using sql server job agent."

  1. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2016/07/27/introduction-to-sql/

    ReplyDelete
  2. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2016/07/27/introduction-to-sql/

    ReplyDelete

Life Is Complicated, But Now programmer Can Keep It Simple.