Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Automated SQL Job to Backup Database(s)

By Venkatesh Krishnamurthy,

Automated SQL Job to Backup All Database(s)


This is a simple script to backup your new databases and remove the backup schedule for deleted database in SQL Server 2005/2008.

The backup strategy of my script is given as below:

  1. Full backup - every Friday at 7 PM.
  2. Differential - every day at 8 PM.
  3. Transactional Log - every 2 hours once.
  • (You can change the backup strategy as par your wish, for doing you needs to change the job schedules under sp_add_jobschedule and update date, time and Schedule type)

Pre-Step to be made:

Creating Backup folder

  1. Create a shared folder in your Backup server if the backup need to be move to Centralized server and give write access to SQL service account.
  2. Otherwise if the backup is in local server then create a folder in any one of the local drive and give write access to SQL Service account.

Update the following variables in the script

@BackupServerNameInput - Enter the Central Backup Server Name

  • Example : 'MYBACKUPSERVER' - If the backup need to be done in Network Backup Server (Refer Line no : 20 and 460)
  • Example : 'LOCALSQLSERVER' - If the backup is in Local host. (Refer Line no : 20 and 460)

@BackupShareInput - Enter the Share / Local path name.

  • Example : '\\MYBACKUPSERVER\SQLBACKUP' - where MYBACKUPSERVER - Central Backup Servername and SQLBACKUP is the shared folder in MYBACKUPSERVER and SQL Service account will have write access to this folder. (Refer Line no : 21 and 461)
  • Example : 'D:\SQLBACKUP' - If you are planning to take the Backup in local disk. (Refer Line no : 21 and 461)

Note : If you are planning to create backup jobs and start full backup immediately once you implement the job, uncomment the last 2 lines in the script.

How can I execute the script?

Simply execute the SQL script and it will create a Job named as 'Create_Backup_Jobs', once you execute the job it will create backup jobs for every databases.

Total article views: 2577 | Views in the last 30 days: 14
Related Articles

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue


Creating Folders Using VB and Recursion

Read this article to learn how to use recursion to simply the task of creating multiple levels of fo...


script for backup files

script for backup files


Script to restore red gate backups automatically from a folder.

Restore of red gate backup files using a SQL Script


SQL SERVER 2008 Backup job/Script



Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones