Transaction Logs - Job Script
I've included this job script, because it is used by the scheduler scripts that I've also posted. This job is a little more involved than most may need... It first checks to see if certain jobs are running. Is so, it waits for a while, and then checks again. After that it drops the existing dump devices, and then recreates them for each database (MAKE SURE TO CHANGE THE @FILE LOCATION FOR THE DUMP DEVICE). Then it deletes the old backup files, and tries to do a differential DB backup. If that fails, it tries a full DB backup... After trying twice it continues with a quick truncate of the transaction log. If it completes the backup without errors, it stops the job. If not it then resets the database options, and retries the backup. (PLEASE LOOK AT EACH JOB STEP, AND MODIFY/REMOVE ANY PORTIONS THAT SHOULD NOT APPLY OR THAT MAY SPECIFY PHYSICAL PATHS THAT DON'T EXIST ON YOUR SERVER).
-- Script generated on 1/4/2002 12:24 PM
-- By: Gabe Green
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Transaction Logs')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Transaction Logs'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Transaction Logs'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Transaction Logs', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'No other job running', @command = N'DECLARE
@result int,
@is_sysadmin INT,
@job_owner sysname,
@tries int
set @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N''sysadmin''), 0)
set @job_owner = SUSER_SNAME()
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
set @tries = 0
set @result = 1
while @result>0 and @tries<60
begin
truncate table #xp_results
INSERT INTO #xp_results
EXECUTE master..xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
SELECT @result=count(*)
FROM #xp_results x
INNER JOIN msdb..sysjobs s
ON x.job_id=s.job_id
WHERE x.running>0
AND s.[name] not in (
''Transaction Logs'',
''Tri-Weekly Job Scheduler'',
''Hourly Job Scheduler'',
''Daily Job Scheduler'',
''Master Job Scheduler''
)
SET @result=ISNULL(@result,-1)
set @tries = @tries + 1
IF(@RESULT>0)
waitfor delay ''00:00:15''
end
DROP TABLE #xp_results
IF(@RESULT>0)
RAISERROR (''Job Running'', 16, 1)
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'remove old trn 1', @command = N'del /s g:\backups\logs\*.trn', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'remove old bak 1', @command = N'del /s g:\backups\databases\*.bak', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'remove old bak 2', @command = N'del /s j:\mssql7\backup\*.bak', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'remove old trn 2', @command = N'del /s j:\mssql7\transaction_logs\*.trn', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 6, @step_name = N're-create devices', @command = N'declare
@db varchar(500),
@device varchar(500),
@file varchar(500)
begin
declare #cd cursor local fast_forward for
select [name] from master..sysdatabases
where [name] not in (''tempdb'',''master'')
open #cd
fetch next from #cd into @db
while(@@fetch_status=0)
begin
set @device=''d''+replace(@db,'' '',''_'')
set @file=''j:\mssql7\backup\''+replace(@db,'' '',''_'')+''.bak''
exec(''sp_dropdevice '' + @device)
exec(''sp_addumpdevice ''''disk'''',''''''+@device+'''''',''''''+@file+''.bak'''''')
fetch next from #cd into @db
end
close #cd
deallocate #cd
end
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 7, @step_name = N'backup logs', @command = N'declare
@db varchar(500),
@device varchar(500),
@file varchar(500),
@cmd varchar(500),
@tries tinyint
begin
declare #cd cursor local fast_forward for
select [name] from master..sysdatabases
where [name] not in (''tempdb'',''master'')
open #cd
fetch next from #cd into @db
while(@@fetch_status=0)
begin
set @device=''d''+replace(@db,'' '',''_'')
set @file=''j:\mssql7\backup\''+replace(@db,'' '',''_'')+''.bak''
set @cmd = ''del ''+@file
set @tries=0
--exec(''backup log [''+@db+''] to ''+@device)
--exec(''sp_addumpdevice ''''disk'''',''''''+@device+'''''',''''j:\mssql7\backup\''+@db+''.bak'''''')
exec(''backup database [''+@db+''] to ''+@device+'' with differential, retaindays=0, init'')
while (@tries<2) and (@@error in (3266, 3013))
begin
set @tries=@tries + 1
if @tries=1
begin
exec master..xp_cmdshell @cmd
exec(''backup database [''+@db+''] to ''+@device+'' with differential, retaindays=0, init'')
end
else
begin
exec(''backup database [''+@db+''] to ''+@device+'' with retaindays=0, init'')
end
end
exec(''backup log [''+@db+''] with truncate_only'')
--exec(''DBCC SHRINKDATABASE ([''+@db+''], 10)'')
fetch next from #cd into @db
end
close #cd
deallocate #cd
end
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 8, @step_name = N'db options', @command = N'declare
@db varchar(500)
begin
declare #cd cursor local fast_forward for
select [name] from master..sysdatabases
where [name] not in (''master'',''tempdb'')
open #cd
fetch next from #cd into @db
while(@@fetch_status=0)
begin
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''dbo use only'''', @optvalue=''''false'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''single user'''', @optvalue=''''false'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''auto create statistics'''', @optvalue=''''true'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''auto update statistics'''', @optvalue=''''true'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''autoshrink'''', @optvalue=''''true'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''select into/bulkcopy'''', @optvalue=''''true'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''torn page detection'''', @optvalue=''''true'''''')
exec(''exec sp_dboption @dbname=''''''+@db+'''''', @optname=''''trunc. log on chkpt.'''', @optvalue=''''false'''''')
fetch next from #cd into @db
end
close #cd
deallocate #cd
end
', @database_name = N'master', @server = N'', @database_user_name = N'dbo', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 9, @step_name = N'backup logs (try again)', @command = N'declare
@db varchar(500),
@device varchar(500),
@file varchar(500),
@cmd varchar(500),
@tries tinyint
begin
declare #cd cursor local fast_forward for
select [name] from master..sysdatabases
where [name] not in (''tempdb'',''master'')
open #cd
fetch next from #cd into @db
while(@@fetch_status=0)
begin
set @device=''d''+replace(@db,'' '',''_'')
set @file=''j:\mssql7\backup\''+replace(@db,'' '',''_'')+''.bak''
set @cmd = ''del ''+@file
set @tries=0
--exec(''backup log [''+@db+''] to ''+@device)
--exec(''sp_addumpdevice ''''disk'''',''''''+@device+'''''',''''j:\mssql7\backup\''+@db+''.bak'''''')
exec(''backup database [''+@db+''] to ''+@device+'' with differential, retaindays=0, init'')
while (@tries<2) and (@@error in (3266, 3013))
begin
set @tries=@tries + 1
if @tries=1
begin
exec master..xp_cmdshell @cmd
exec(''backup database [''+@db+''] to ''+@device+'' with differential, retaindays=0, init'')
end
else
begin
exec(''backup database [''+@db+''] to ''+@device+'' with retaindays=0, init'')
end
end
exec(''backup log [''+@db+''] with truncate_only'')
--exec(''DBCC SHRINKDATABASE ([''+@db+''], 10)'')
fetch next from #cd into @db
end
close #cd
deallocate #cd
end
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 1, @retry_interval = 30, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 10, @step_name = N'Backup DB', @command = N'exec sp_start_job @job_name=''Backup ''''DB To J''''''', @database_name = N'msdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Transaction Logs', @enabled = 1, @freq_type = 4, @active_start_date = 20010606, @active_start_time = 200, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 2, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 180059
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @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: 
