Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Create a database backup job using SQL Server Management Studio

Create a database backup job using SQL Server Management Studio

Rate This
  • Comments 22

 

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 DATABASE DemoDB
GO
 
USE DemoDB
GO
-- Create a table 
CREATE TABLE TestData(id int)
GO
 
-- Insert sample data
INSERT INTO TestData(id) VALUES(1)
INSERT INTO TestData(id) VALUES(2)
GO
 
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”

image

 

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

image

Click on”New” to create a new job step

image

 

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

 

Click Ok to add this step to the job

image

 

Click Ok to create this job

image

 

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”

image

 

You will see a job start progress dialog

image

 

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

 

image

 

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

image

 

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

image

 

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
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 2 and 6 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

  • thanks....so nice

  • Thank you!

  • Thank you ,but I found all backup files are saved to xxx.bak folder can't be openned.so 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 (22 items) 12