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.
2. In New Job dialog box select General and define the job name under name section.
3. Select Steps option and click on New button to create the steps.
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.
5. In Advanced section we will capture the Job Log and select the job behaviour option based on Job action. Click Ok.
6. Click on Schedules to difine the specific time on which the action occur. Click New.
7. Under this section we will select the Schedule Type and Frequency to set the Job occurrence. Click Ok.
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.
9. In Start jobs dialog you will able to view the status of the Job.
After follow the above steps, The backup file generate actomatically set 5:00 AM.