Create a database backup job using SQL Server Management Studio and SQL server Agent(daily)

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”

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

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

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. 

Next Step: Assign this Job to Scheduler

1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and right click on the Sql Server Agent \ Jobs \ Your Job.

Select Properties and click.

2) Click on the Schedules Page on the left, then click New… under Schedule list.

If the job already has a schedule, you can edit it or remove it.

 

3) Enter a name for the schedule, then select the type of schedule you want.

Here is some information:

Schedule Type: the option are straightforward. For “Start whenever the CPUs become idle” you need to set up an idle CPU condition (see http://msdn.microsoft.com/en-us/library/ms189065.aspx)

Enabled: here you can disable a schedule without deleting it.

You can select what type of occurrence, the days of the week and/or how many days/weeks/months to skip between executions.

For daily schedules, you can specify how many seconds/minutes or hours between executions.

Note: By default, Sql Agent will not execute a job more often than four times a minute (every 15 seconds). In general, the scheduler cannot be used for “real time” events. The job start can be delayed depending on the system load.

 

Check that the next job execution will be triggered at the right time.

After creating or updating a schedule, you can check the next execution date and time.

Preconditions: Sql Agent has to be running for the next execution date/time to be calculated.

In SSMS, expand the Sql Server Agent node under Object Explorer, then double-click on Job Activity Monitor. Look for your job name and check the column “Next Run”.

Check the job was executed at the right time after the scheduled time has passed.

You can check the job history at any time to confirm that the schedule was triggered correctly and the job succeeded.

Just right click on the job and select “View History”.