Setup SQL Job for backup database

Data is a very important part of every office, every human being and. Data are the one kind of information, every software, as well as a web application, have their data to be stored in the database. there are many database software one of them is Microsoft SQL Server. MS SQL Server is the most used database software for every organization some time we forget to keep backup. if we do not keep backup of the database there will be a chance of losing data in case of accidental delete or corrupt of database and database software To make an auto backup in SQL server we need to set up SQL Job for Backup Database. Here I am showing you how to set up SQL Job for backup database. please follow the following step to setup SQL Job for SQL Database

Sample Backup Query (Please change the database name and backup path as per your database and drivve)


SQl QUERY
declare @name varchar(max)
set @name= 'Full path\databasebackupname_'+(replace(convert(varchar,getdate(),112) + convert(varchar,getdate(),114),':',''))
BACKUP DATABASE Databasename
TO DISK =@name
)

eg.

declare @name varchar(max)
set @name= 'E:\MEDIFLOW_BACKUP\Mediflowbackup_'+(replace(convert(varchar,getdate(),112) + convert(varchar,getdate(),114),':',''))
BACKUP DATABASE Mediflow
TO DISK =@name

Set Up SQL Job for backup database


1. Expand The Sql Server Agent to create the sql job

Set Up SQL Job for backup database

2. Right click on Jobs and click new job as shown in the following figure


Set Up SQL Job for backup database

3. Give job name in the name box and click on the step and click on the new to create a new step.

Set Up SQL Job for backup database

Set Up SQL Job for backup database


Click on New to create new step after selecting Steps tab

Set Up SQL Job for backup database


Give the name of step in step name section and select Transact-SQL script (T-SQL) in type select database name in the database tab and write SQL script in the command box and click ok

(


declare @name varchar(max)
set @name= 'Full path\databasebackupname_'+(replace(convert(varchar,getdate(),112) + convert(varchar,getdate(),114),':',''))
BACKUP DATABASE Databasename
TO DISK =@name
)

eg.

declare @name varchar(max)
set @name= 'E:\MEDIFLOW_BACKUP\Mediflowbackup_'+(replace(convert(varchar,getdate(),112) + convert(varchar,getdate(),114),':',''))
BACKUP DATABASE Mediflow
TO DISK =@name

Set Up SQL Job for backup database

Click on schedules in the to create a schedule for the backup database, click ok after select schedules

Set Up SQL Job for backup database

Create a schedule for backup your database. this will be daily weekly monthly and also you can set the timing too


Set Up SQL Job for backup database


enable SQL Server Agent by right clicking on SQL Server Agent





0 Comments