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:
- Full backup - every Friday at 7 PM.
- Differential - every day at 8 PM.
- 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
- 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.
- 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.