Article

Create and schedule database backup job using SQL Server Management Studio

03 Feb 2018 Kamal Pratap
0 Comments 760 Views



Here we explain how to configure automatic, scheduled database backups in MS SQL Server.

In my last example Creating SQL server database backup file in Asp.Net with C# we use the Asp.net C# to create the database backup. Today, I am going to describe, how to create a SQL Server Agent job step, to take database backup file in (.bak) format. I am giving the example to take SQL server database backup (.BAK) file at specific time using job scheduler.

Create a T-SQL script to take the database backup.

BACKUP DATABASE [TestDB]
TO  DISK = N'F:\Backup\TestDB.bak'

By using this script we will take the backup daily at specific time. We will use the job scheduler to execute this script daily to achieve the task.

1. In Object Explorer, Connect to SQL Server, Expand SQL Server Agent, Expand Jobs,  Right click on Jobs and select New Job options.

SQL Server Database Backup

2. In New Job dialog box select General and define the job name under name section.

SQL Server Database Backup

3. Select Steps option and click on New button to create the steps.

SQL Server Database Backup

4. Enter Step Name and select Database name in which to execute the T-SQL script. Enter database backup T-SQL script under command section and choose Advanced option.

SQL Server Database Backup

5. In Advanced section we will capture the Job Log and select the job behaviour option based on Job action. Click Ok.

SQL Server Database Backup

6. Click on Schedules to difine the specific time on which the action occur. Click New.

SQL Server Database Backup

7. Under this section we will select the Schedule Type and Frequency to set the Job occurrence. Click Ok.

SQL Server Database Backup

8. If you want to test the Job you have to execute the Job manually. Right click on Job and select Start Job at Step option.

SQL Server Database Backup

9. In Start jobs dialog you will able to view the status of the Job.

SQL Server Database Backup

SQL Server Database Backup

After follow the above steps, The backup file generate actomatically set 5:00 AM.

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment