Schedule Script - SQL Server Forums

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

Data Corruption Issues

Schedule Script

Author

Topic

DaveShaw
Starting Member

3 Posts

Posted - 2005-09-08 : 19:05:13

Hi All,

I know this is not the script forum, but this script will only apply to you guys.

I have to look after lots of SQL Servers and I have to check the DBs before HW repairs and large DTS's. I wrote this to help with the task of scheduling the jobs. All I have to do now is ISQLW onto the server, run this and check the file in the morning.

Anyone who has any improvements please Mail me.

------------------------------------------------------------------------------------------
-- Script to Generate a DBCC CHECKCATALOG and CHECKDB at 0105hrs Tomorrow Morning. --
-- Output from Job will be C:\[ddmmyy].dbcc e.g. C:\230505.dbcc --
-- Job will delete if successful.
-- --
-- Author: Dave Shaw, © 2005-09-05 --
-- Version 1.5 --
-- --
-- This Script will produce 4 outputs if Create Bit=1: --
-- > 1, from the NET START SQLSERVERAGENT. --
-- > 2, from the Job ID From the sysjobs table of the job --
-- > 3, Message informing you if the Create Job/Delete Job Succeeded/Failed. --
-- > 4, the information from sp_get_composite_job_info (all the job info) --
-- This Script will produce 1 output if Create Bit=0: --
-- > 1, Message informing you if the Create Job/Delete Job Succeeded/Failed. --
-----------------------------------------------------------------------------------------

--User Defined Options
DECLARE @j_date TINYINT --Date Job will run - Default: 1
SET @j_date = 1 --use 0 for today, 1 for tomorrow etc.
DECLARE @j_time INT --Time Job Will Run - Default: 010500
SET @j_time = 010500 --Format 24hrs hhmmss
DECLARE @j_uid NVARCHAR(25) --Username to run the Job as - Default: sa
SET @j_uid = 'sa' --Set to Database Admin Username
DECLARE @create_bit BIT --Flag to create DBCC jobs - Default: 1
SET @create_bit = 1 --If 0 Delete Only - Do Not Create Jobs, If 1 Delete then create Jobs

--Main Code
BEGIN TRANSACTION
--Declate Variables for Job Creation
-- DECLARE @name NVARCHAR(40) --database names (Use on SQL 7.0)
DECLARE @name SYSNAME --database names (Use for SQL2000 +)
DECLARE @CheckCatalog NVARCHAR(4000) --SQL for CheckCatalog Step
DECLARE @CheckDB NVARCHAR(4000) --SQL for CheckDB Step
DECLARE @JobID BINARY(16) --ID for New Job
DECLARE @ReturnCode INT --Return code from creating jobs
DECLARE @job UNIQUEIDENTIFIER --ID of the Old Job

--Code for getting formatted dates for use in the file name and date to run Checks.
DECLARE @dt NVARCHAR(8) --Date in yyyymmdd format
DECLARE @df NVARCHAR(14) --file name for results
DECLARE @dm NVARCHAR(2) --stores month
DECLARE @dd NVARCHAR(2) --stores day
DECLARE @dy NVARCHAR(4) --stores year
DECLARE @tm DATETIME --holds date

--Get the date of tomorrow and format it yyyymmdd e.g. 20051225 for 25/12/2005
SET @tm = GETDATE() + @j_date
SET @dy = CAST(YEAR(@tm) as NVARCHAR)
SET @dm = CAST(MONTH(@tm) as NVARCHAR)
SET @dd = CAST(DAY(@tm) as NVARCHAR)

IF (LEN(@dm) = 1) SET @dm = '0' + @dm
IF (LEN(@dd) = 1) SET @dd = '0' + @dd

SET @dt = @dy + @dm + @dd

--Create the filename for the output based on todays date with C:\[ddmmyy].dbcc
SET @tm = GETDATE()
SET @dy = CAST(YEAR(@tm) as NVARCHAR)
SET @dm = CAST(MONTH(@tm) as NVARCHAR)
SET @dd = CAST(DAY(@tm) as NVARCHAR)

IF (LEN(@dm) = 1) SET @dm = '0' + @dm
IF (LEN(@dd) = 1) SET @dd = '0' + @dd

SET @df = 'C:\' + @dd + @dm + RIGHT(@dy,2) + '.dbcc'
--End of Date Code

--Set Varibles to default values
SET @ReturnCode = 0
SET @CheckCatalog = ''
SET @CheckDB = ''

--Get the Database names and create the SQL for the steps
DECLARE DBCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE has_dbaccess(name) = 1 --check access.

OPEN DBCursor
FETCH DBCursor INTO @name

WHILE @@FETCH_STATUS >= 0
BEGIN
IF (@name <> 'Northwind' AND @name <> 'pubs')
BEGIN
SET @CheckCatalog = @CheckCatalog + 'DBCC CHECKCATALOG (''' + @name + ''') WITH NO_INFOMSGS' + CHAR(13) + 'GO' + CHAR(13)
SET @CheckDB = @CheckDB + 'DBCC CHECKDB (''' + @name + ''') WITH NO_INFOMSGS' + CHAR(13) + 'GO' + CHAR(13)
END --if
FETCH DBCursor INTO @name
IF (@@ERROR <> 0) GOTO QuitWithRollback
END --end while

DEALLOCATE DBCursor
--End of code for creating SQL

--Enable the SQL Server Agent Service
EXECUTE @ReturnCode = master.dbo.xp_cmdshell 'NET START "SQLSERVERAGENT"'

--Look for an old job and delete it
SELECT @job = job_id FROM msdb.dbo.sysjobs WHERE name = N'Support DBCC Check'
IF @job IS NOT NULL EXECUTE msdb.dbo.sp_delete_job @job_id = @job

--IF Create Bit Flag is set End Program
--This will just delete the job.
IF @create_bit <> 1
BEGIN
COMMIT TRANSACTION
GOTO EndSave
END

--Create Job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'Support DBCC Check', @enabled = 1, @owner_login_name = @j_uid, @description = 'DBCC Check automatically generated for Support to scan all databases. ', @category_name = N'[Uncategorized (Local)]', @notify_level_eventlog = 2,@delete_level = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create Check Catalog Step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Check Catalog', @command = @CheckCatalog, @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 = @df, @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create Check DB Step
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Check Database', @command = @CheckDB, @database_name = N'master' , @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 2, @retry_attempts = 0, @retry_interval = 1, @output_file_name = @df, @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

--Set Start Step
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id=1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create Job Schedule
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'DBCC Schedule', @enabled = 1, @freq_type = 1, @active_start_date = @dt, @active_start_time = @j_time
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Create Job Server
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @@SERVERNAME, @automatic_post = 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Get the Id of the New Job
SELECT @JobID as "Job ID"
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

--Save the Transaction or Roll it back if there were errors.
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

--Checks to see if the job has been created and informs the user.
DECLARE @JobID as BINARY(16)
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE name = N'Support DBCC Check'

IF @JobID IS NOT NULL
BEGIN
--Job Creation OK
SELECT 'Job Creation Successful/Delete Job Failed - Please Check the Next Run Time and Date Below.' as MESSAGE
EXECUTE msdb.dbo.sp_get_composite_job_info @job_id = @JobID
END
ELSE
BEGIN
--Job Creation Failed
RAISERROR ('Job Creation Failed/Delete Job Successful - DBCC Job Cannot Be Located. Please Re-Run the Script or Create the Job in SQL Ent Mgr.',16,1)
END
--End.

Thanx!
Dave Shaw.

History Admires the Wise, but Elevates the Brave - Edmund Morris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources