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.