Schedule SQL Job for SQL Database Backup

 

Today we will see how we can schedule to take daily backup of our database.

Before i start explaining the step i am confirming the version of SQL based on which i am writing this post.

Process will remain same may be screen bit different in different versions of SQL.



At first we will ensure that SQL Server Agent is running, in case it is not right click and Start it.



Expand the SQL Server Agent Folder, Select Jobs, Right click and select New Job.



Provide Name & Description to your New Job.



Select Page Steps & New from Bottom of the page to define Step for your Job.



Give name to the Step.

Select Type as Transact Script (T-SQL).

Select Database Name in my case i am selecting Demo Database NAV (9-0)

Write the Script as shown below:



Here is the Script for your ready reference:

{-- Script Start

SET QUOTED_IDENTIFIER off

select getdate() "Start Time"

set nocount on
declare @dbname varchar(36),@cmd varchar(255)

declare dbname_cursor cursor
for select name from master..sysdatabases where name = 'Demo Database NAV (9-0)'
order by name

open dbname_cursor
fetch dbname_cursor into @dbname

while @@fetch_status = 0
begin
DECLARE @DATE VARCHAR(36)
SELECT @DATE = (select CONVERT(char(8),DATEADD(dd,-30,GETDATE()),112))
if DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
begin
select @cmd ='backup database ['+@dbname+'] to DISK=''C:\User Data\SQL Backup\'+@dbname+'.bak'' with init';
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname
end

close dbname_cursor

deallocate dbname_cursor

select GETDATE() "End Time"

--Script End}

Although above script is for multipurpose like if you want to backup all database on your SQL Server, you can make small tweaking and you are done.

If you would like to backup all the databases on a particular SQL Server, then make the following changes to the code above.

Replace following line of code:

for select name from master..sysdatabases where name = 'Demo Database NAV (9-0)'

with the following line of code:

for select name from master..sysdatabases where name != 'tempdb'

This will create a backup file for each database on the server, except for the temp database.

Next : Switch to Advanced Page.

Define other parameters as shown in below screen as per your requirement.



On Selecting OK, you will return to Job window.



Select Schedule Page:

Define your Schedule as per your requirement.



Select OK to return to Job Page.



Select OK to close and save the JOB.

Now our Job is  created and scheduled.

You can see defined Jobs and its Status using Job Activity Monitor.

Before Job is executed



After executed you can see the Status as.



Post execution of Job here is the backup file created at defined path in the Script. Above script will overwrite the file on next execution.

If you want to create new file on every execution you need to modify the script for file name by adding timestamp or any other logic you desire.



I will come up with more information in my upcomming posts.

thats all for this post.