We can easily create a database backup job to backup an user database. We can done this by using MSSQL management studio. We can follow the below mentioned steps and create a MSSQL backup job.
How to create Database backup job from MS SQL Management Studio
First We need to create a test database. We can create test database from SQL management studio by following the below steps.
- Login to SQL management studio.
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- Right-click Databases, and then click New Database.
- In New Database, enter a database name, here we can use the name as DemoDB.
- To create the database by accepting all default values, click OK.
Then we are creating new MS SQL Agent Job.
In Object Explorer, connect to an instance of the SQL Server, expand “SQL Server Agent”, expand Jobs and then right click Jobs and select the option “New Job”.
In the General page, give a name to the SQL Agent job, here we can use the name as “Test Backup Job”.
The next step is to create a backup job step.
Select the option “Steps”
In the Steps page, create a new job step by click on “New” button.
Then we are naming the job steps, here we can use “Backup Job Step” and we can use below script 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’
After entering the name and add script to the command field, click Ok to add this step to the job.
Then click Ok to create this job from the “Steps” page.
We can view the created job under Jobs folder in Object Explorer.
To Start this job right click on “Test Backup Job” (under SQL Server Agent –> Jobs ) and click “Start job at Step”.
Then we can see a window, that states the job start progress.
We can see “Success” status after completing the job.
The status of the each Agent job can view from the Job History and Logs.
To view Job History and logs in Object Explorer, Under SQL Server Agent Jobs , select and right click the Agent Job we have created and select “View History”. We can see the recent job execution history and the results of the job execution in log viewer.
By following the above mentioned steps, we can easily create a MS SQL Agent job step to backup a MSSQL DB.