SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts 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: 2627 | Views in the last 30 days: 3
Related Articles

Script to create datestamp folder causing issue

Script to create datestamp folder causing issue


PowerShell Query to remove backup folders against multiple servers

Remove backup folders from multiple servers.


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