Create a database backup job using SQL Server Management Studio

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s