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 nameopen dbname_cursor
fetch dbname_cursor into @dbnamewhile @@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
endclose 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.
ARKSD Consultancy Private Limited 
You must be logged in to post a comment.