Software Architecture – Backup all database of SQL Server via Job/schedule

Problem Definition:
In development environment, often it is required to take backup of all databases on weekly basis. During whole week, development is going on by different teams on different databases, so it is very vital to have a backup mechanism for all the database.
So objective is to take backup of all databases in a SQL Server Instance on first day of week during lunch time that is 2:00 pm

In SQL Server, Jobs and schedules are used to run any action with specific time plan.
SQL Server agent must be running to run the jobs.

First step is to create a job, by doing a right click on job node in object explorer of SQL Server.

Click on steps to define a action to be performed by putting following T-SQL in command text

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'F:\DBWeeklyBackup\'  


SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor

Step window will look like as:

Click on schedule to define the time plan for job to be run as.

Define the notification for job as:

Test Job by clicking on “Start Job At Step”

It will create backup of all databases in SQL Server at F:\DBWeeklyBackup\ with date appended at the end.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: