Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

Create a database backup job using SQL Server Management Studio

Create a database backup job using SQL Server Management Studio

Rate This
  • Comments 28


SQL Server Management Studio  can be used to create a database backup job to backup an user database. Here are the steps and User interface workflow to create a simple backup job, run the job and view results

1) Create a  demo database and insert sample data using the following script.
-- Create Demo Database
-- Create a table 
CREATE TABLE TestData(id int)
-- Insert sample data
SELECT * from DemoDB.dbo.TestData


2) Create SQL Agent Job

In Object Explorer, Connect to SQL Server, Expand “SQL Server Agent” node, Expand Jobs;  right click ; select menu “New Job”



Type in name of the SQL Agent Job as “Test Backup Job”
3) Create a backup job step
Select the page “Steps”


Click on”New” to create a new job step



Type in name for job step as “Backup Job Step” and T-SQL statement to backup database
-- Script to backup database
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\DemoDB.bak' 


Click Ok to add this step to the job



Click Ok to create this job



4) View newly created job under Jobs folder in Object Explorer; To Start this job right click on “Test Backup Job” (under SQL Server Agent –> Jobs node ) ; select “Start job at Step”



You will see a job start progress dialog



After Job completion, you would see the “Success” status set for this job




5) View Job History and logs – in Object Explorer, right click on “Test Backup Job”  (under SQL Server Agent –> Jobs node); select “View History”



You would see the recent job execution history and job step results  in log viewer



Following above steps creates a simple SQL Agent job with one job step to backup a SQL Server database.   You can create a schedule and run this job on  specific scheduled interval / specific time. Please take a look at Tibi's blog entry


This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • That is very helpfu for a new user. Thank you very much

  • Thanks

  • Hi!!!

    I need this tutotial...

    Thank you very much!!!

  • Like this

  • Useful

  • why do u need to hide ur servername? how we can reach it :lol:

    u forget to hide at first pic.

    and the last word. ur server name is REDMOND and u will be HACKED :)))

    good tutorial ty

  • very... very ... Help full...thanks  

  • Thanks

  • Thank Dear. thats very usefull for bigner

  • nice

  • Thank you!

  • Thank you ,but I found all backup files are saved to xxx.bak folder can't be I want to delete the older backup files,how to do?

  • Awesome One....I was finding this from a long....Thanq

  • thanks, i already did this but when i try to run the job, it executes step 1 and when it had to do the actual backup of the dbase it comes with an error message that [the owner of the Backup database job does not have access to the server]  I put the job owner as an administrator account.

  • Very very useful for beginners

Page 1 of 2 (28 items) 12