Automated SQL Job to Backup all Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Automated SQL Job to Backup all Databases

Author

Topic

vk.venkatesh
Starting Member

1 Post

Posted - 2009-12-24 : 02:19:35

Automated SQL Job to Backup all Databases
This is a simple and powerful SQL script which you never experienced, and is used to backup your new databases and remove the backup schedule for deleted database automatically in SQL Server 2005/2008.

The backup strategy of my database is as given 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:
1) Creating Backup folder
a. 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.
b. 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.

2) Update the variables in the script
a. @BackupServerNameInput – Enter the Central Backup Server Name
i. Example : ‘MYBACKUPSERVER’ - If the backup need to be done in Network Backup Server (Refer Line no : 20 and 460)
ii. Example : ‘LOCALSQLSERVER’ – If the backup is in Local host. (Refer Line no : 20 and 460)

b. @BackupShareInput – Enter the Share / Local path name.
i. 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)
ii. Example : ‘D:\SQLBACKUP’ – If you are planning to take the Backup in local disk. (Refer Line no : 21 and 461)

3) If you are planning to start creating backup jobs and start full backup immediately once you implement the job uncomment the last 2 lines.
How does It work?
After changing the above values, simply execute the script. This script will create a job “Create_Backup_Jobs” once you execute this job it will create Backup jobs (Full, Differential and Transactional Log based on recover model of the database) for every databases in the SQL instance.Please fell free to write vk.venkatesh@hotmail.com
SQL Script:

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO

/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
????@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
????@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)

SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput

/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
????SET @CI = @ServerName
ELSE
????SET @CI = @ServerName + '_' + @CI

/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName

/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/

/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
????????@enabled=1,
????????@notify_level_eventlog=0,
????????@notify_level_email=0,
????????@notify_level_netsend=0,
????????@notify_level_page=0,
????????@delete_level=0,
????????@description=N'Full Backup',
????????@category_name=N'BACKUP',
????????@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
????????DECLARE @Db_Name VARCHAR(200)
????????DECLARE @BackupShare VARCHAR(1000)
????????DECLARE @FileName VARCHAR(1000)
????????SET @Db_Name = ''' + @DbName + '''
????????SET @BackupShare = ''' + @ShareName+ '''
????????SET @FileName = convert(varchar, getdate(), 120)
????????SET @FileName = stuff (@FileName, 11, 1, ''_'')
????????SET @FileName = stuff (@FileName , 14, 1, ''-'')
????????SET @FileName = stuff (@FileName, 17, 1, ''-'')
????????SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
????????SET @BackupShare = @BackupShare + ''\'' + @FileName
????????backup database @Db_Name TO DISK= @BackupShare with init'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
????????@step_id=1 ,
????????@cmdexec_success_code=0,
????????@on_success_action=1,
????????@on_fail_action=2,@subsystem=N'TSQL',
????????@command=@CommandString,????
????????@database_name=@DbName,
????????@flags=0,
????????@retry_attempts=3,
????????@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
????????@enabled=1,
????????@freq_type=8,
????????@freq_interval=32,
????????@freq_subday_type=1,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=1,
????????@active_start_date=20080929,
????????@active_end_date=99991231,
????????@active_start_time=190000,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
????????@enabled=0,
????????@freq_type=1,
????????@freq_interval=0,
????????@freq_subday_type=0,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20081003,
????????@active_end_date=99991231,
????????@active_start_time=101920,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/

END
/*Create the Full Backup job - end */

/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
????????@enabled=1,
????????@notify_level_eventlog=0,
????????@notify_level_email=0,
????????@notify_level_netsend=0,
????????@notify_level_page=0,
????????@delete_level=0,
????????@description=N'Diff Backup',
????????@category_name=N'BACKUP',
????????@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
????????DECLARE @Db_Name VARCHAR(200)
????????DECLARE @BackupShare VARCHAR(1000)
????????DECLARE @FileName VARCHAR(1000)
????????SET @Db_Name = ''' + @DbName + '''
????????SET @BackupShare = ''' + @ShareName+ '''
????????SET @FileName = convert(varchar, getdate(), 120)
????????SET @FileName = stuff (@FileName, 11, 1, ''_'')
????????SET @FileName = stuff (@FileName , 14, 1, ''-'')
????????SET @FileName = stuff (@FileName, 17, 1, ''-'')
????????SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
????????SET @BackupShare = @BackupShare + ''\'' + @FileName
????????backup database @Db_Name TO DISK=@BackupShare with differential'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
????????@step_id=1 ,
????????@cmdexec_success_code=0,
????????@on_success_action=1,
????????@on_fail_action=2,@subsystem=N'TSQL',
????????@command=@CommandString,????
????????@database_name=@DbName,
????????@flags=0,
????????@retry_attempts=3,
????????@retry_interval=5
????????
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
????????@enabled=1,
????????@freq_type=8,
????????@freq_interval=95,
????????@freq_subday_type=1,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=1,
????????@active_start_date=20080213,
????????@active_end_date=99991231,
????????@active_start_time=200000,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
????????@enabled=0,
????????@freq_type=1,
????????@freq_interval=0,
????????@freq_subday_type=0,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20081003,
????????@active_end_date=99991231,
????????@active_start_time=101920,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

END

/*Create the Diff Backup job - end*/

/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
????????@enabled=1,
????????@notify_level_eventlog=0,
????????@notify_level_email=0,
????????@notify_level_netsend=0,
????????@notify_level_page=0,
????????@delete_level=0,
????????@description=N'Log Backup',
????????@category_name=N'BACKUP',
????????@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
????????DECLARE @Db_Name VARCHAR(200)
????????DECLARE @BackupShare VARCHAR(1000)
????????DECLARE @FileName VARCHAR(1000)
????????SET @Db_Name = ''' + @DbName + '''
????????SET @BackupShare = ''' + @ShareName+ '''
????????SET @FileName = convert(varchar, getdate(), 120)
????????SET @FileName = stuff (@FileName, 11, 1, ''_'')
????????SET @FileName = stuff (@FileName , 14, 1, ''-'')
????????SET @FileName = stuff (@FileName, 17, 1, ''-'')
????????SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
????????SET @BackupShare = @BackupShare + ''\'' + @FileName
????????BACKUP LOG @Db_Name TO DISK=@BackupShare'
????????
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
????????@step_id=1 ,
????????@cmdexec_success_code=0,
????????@on_success_action=1,
????????@on_fail_action=2,@subsystem=N'TSQL',
????????@command=@CommandString,????
????????@database_name=@DbName,
????????@flags=0,
????????@retry_attempts=3,
????????@retry_interval=5
????
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
????????@enabled=1,
????????@freq_type=4,
????????@freq_interval=1,
????????@freq_subday_type=8,
????????@freq_subday_interval=2,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20080213,
????????@active_end_date=99991231,
????????@active_start_time=010000,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
????????@enabled=0,
????????@freq_type=1,
????????@freq_interval=0,
????????@freq_subday_type=0,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20081003,
????????@active_end_date=99991231,
????????@active_start_time=101920,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

END

/*Create the Log Backup job - end*/

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END

CLOSE Database_Cursor

DEALLOCATE Database_Cursor

/*****************************************************************************************************************************/

OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7
SET @Backup_Var = 'STOP'
ELSE
SET @Backup_Var = (SELECT LEFT(@JobName, 7))
IF (@Backup_Var = 'Backup_')
BEGIN
SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName)))
IF (SELECT RIGHT(@JobName2, 3)) = 'Log'
BEGIN
SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4))
SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7))
END
ELSE
BEGIN
SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5))
SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7))
END
IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2)
BEGIN
SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1
PRINT @JobName + ' is deleted'
END
END
FETCH NEXT FROM JobName_Cursor INTO @JobName
END
CLOSE JobName_Cursor
DEALLOCATE JobName_Cursor

/*****************************************************************************************************************************/

END

GO
/******************************************************************************************************************************************/
/* */
/* Here begins the job creation */
/* */
/******************************************************************************************************************************************/

USE [master]

DECLARE @BackupServerName VARCHAR(50)
DECLARE @BackupShare VARCHAR(1000)
--DECLARE @Backup_Var VARCHAR(100)
--DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs
--DECLARE @JobName2 VARCHAR(200)
DECLARE @JobName VARCHAR(200)
DECLARE @CommandString VARCHAR(4000)

-----------------------------------------------------------------------------------------------------------
-- Input section 2/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name
SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename
--SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 2/2 end
-----------------------------------------------------------------------------------------------------------

USE [msdb]

/****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
????????@enabled=1,
????????@notify_level_eventlog=0,
????????@notify_level_email=0,
????????@notify_level_netsend=0,
????????@notify_level_page=0,
????????@delete_level=0,
????????@description=N'Creates backup jobs for all the databases',
????????@category_name=N'BACKUP',
????????@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
????????@step_id=1,
????????@cmdexec_success_code=0,
????????@on_success_action=3,
????????@on_success_step_id=0,
????????@on_fail_action=4,
????????@on_fail_step_id=5,
????????@retry_attempts=3,
????????@retry_interval=5,
????????@os_run_priority=0, @subsystem=N'TSQL',
????????@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
????????@database_name=N'master',
????????@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases

-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------

/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
????SET @CI = @ServerName
ELSE
????SET @CI = @ServerName + ''_'' + @CI

/* Get server and instance name end */

/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName

/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END

/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor

DEALLOCATE Database_Cursor
go'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
????????@step_id=2,
????????@cmdexec_success_code=0,
????????@on_success_action=3,
????????@on_success_step_id=0,
????????@on_fail_action=4,
????????@on_fail_step_id=5,
????????@retry_attempts=3,
????????@retry_interval=5,
????????@os_run_priority=0, @subsystem=N'TSQL',
????????@command=@CommandString,
????????@database_name=N'master',
????????@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
????????@step_id=3,
????????@cmdexec_success_code=0,
????????@on_success_action=3,
????????@on_success_step_id=0,
????????@on_fail_action=2,
????????@on_fail_step_id=0,
????????@retry_attempts=3,
????????@retry_interval=5,
????????@os_run_priority=0, @subsystem=N'TSQL',
????????@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
????????@database_name=N'master',
????????@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
????????@step_id=4,
????????@cmdexec_success_code=0,
????????@on_success_action=1,
????????@on_success_step_id=0,
????????@on_fail_action=2,
????????@on_fail_step_id=0,
????????@retry_attempts=3,
????????@retry_interval=5,
????????@os_run_priority=0, @subsystem=N'TSQL',
????????@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
????????@database_name=N'master',
????????@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
????????@step_id=5,
????????@cmdexec_success_code=0,
????????@on_success_action=2,
????????@on_success_step_id=0,
????????@on_fail_action=2,
????????@on_fail_step_id=0,
????????@retry_attempts=3,
????????@retry_interval=5,
????????@os_run_priority=0, @subsystem=N'TSQL',
????????@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
????????@database_name=N'master',
????????@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
????????@enabled=1,
????????@freq_type=4,
????????@freq_interval=1,
????????@freq_subday_type=1,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20081002,
????????@active_end_date=99991231,
????????@active_start_time=170000,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
????????@enabled=0,
????????@freq_type=1,
????????@freq_interval=0,
????????@freq_subday_type=0,
????????@freq_subday_interval=0,
????????@freq_relative_interval=0,
????????@freq_recurrence_factor=0,
????????@active_start_date=20081002,
????????@active_end_date=99991231,
????????@active_start_time=55710,
????????@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

go

/* Uncomment the below code if you like to create Backup jobs now*************/
/*
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs

*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-24 : 03:28:11

can you include your code within the [ code ] tag ?

also, there are lots of ????? everywhere. Could you clean it up a bit ?

KH
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-01-10 : 10:22:14

Where is the SQL that checks which database to backup?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources