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

 

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


Comments (36)

  1. Jackie says:

    That is very helpfu for a new user. Thank you very much

  2. Durga misra says:

    Thanks

  3. @ndres says:

    Hi!!!

    I need this tutotial…

    Thank you very much!!!

  4. Thepudi says:

    Like this

  5. Pandi says:

    Useful

  6. tech says:

    why do u need to hide ur servername? how we can reach it 😆

    u forget to hide at first pic.

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

    good tutorial ty

  7. Mujtaba says:

    very… very … Help full…thanks  

  8. Atul Sharma says:

    Thanks

  9. Gaurav Singh Tomar says:

    Thank Dear. thats very usefull for bigner

  10. Araya says:

    thanks….so nice

  11. Mozarelli says:

    Thank you!

  12. SAM says:

    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?

  13. Aamin Khan says:

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

  14. Lutie says:

    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.

  15. Sachin says:

    Very very useful for beginners

  16. Andy says:

    Thanks for this information.

  17. Ashish Gupta (DBA) says:

    wonderfull for all… 🙂 🙂

  18. Nitin says:

    Thank you so much for this useful information in easy way.

  19. SinndhuRanganathan says:

    Is ther an option to find put / get a notification when the job get's hanged ?

  20. Yuri Moyses says:

    Can I run one script to backup all bases?

  21. Niraj says:

    Thank you!!! this is helpful for me .. 🙂

  22. Eash says:

    Nice, very useful for fresher's

  23. NR says:

    Your way of explanation make it more EG.

  24. SomeGuy says:

    @tech

    FYI, his database server hostname isn't REDMOND; that is his domain name and he's logged in as seths. Still useless information to the outside world. Great tutorial nonetheless.

  25. Daniel says:

    Good news is this helped me setup a backup for my 68GB MSSQL database.

    Bad news is that my .bak file is now 200GB and I'm out of space on my 300GB drive.

    How can we modify this script to compress the backups, take full instead of incremental, or use other means to ensure our backup file does not grow so large?

  26. Manish says:

    This is Good

  27. Anton says:

    Very nice, thank you.

  28. Giorgi says:

    Many Thanks

  29. Sham says:

    Thanks, good instruction but can you share how to remove file backup after 2 week..

  30. Lakmali says:

    Very nice article.

  31. Ch Tanvir says:

    Wrong Method Maintenance Plan is a easy and self automated way to take backup

    lakkireddymadhu.wordpress.com/…/scheduling-automated-backup-using-sql-server-2008

  32. Abdullah says:

    it is very informative.  but i wanna create a job to do scheduled transaction log backup. please help me with the script or process

  33. G Dummett says:

    Don't forget to add at the end of the script WITH IN or ,INIT if your running a differential backup. This will stop the backup doubling in size every time it is run.

  34. G dummett says:

    Don't forget to add WITH INIT to the end of the script otherwise your backup will double in size every time it is run.

    For differential backup add ,INIT.

    I.E

    BACKUP DATABASE [DemoDB]

    TO  DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupDemoDB.bak' WITH INIT

  35. piccon says:

    please also tell how to schedule it….m trying but it is not taking at that time

  36. Vishwam *** says:

    This is very helpful, But how to send email notification for that we have to do any email settings.

    Thank you in advance