October 14, 2008 at 11:40 am
Hi All
We have to create maintenance plan for all the databases on our all the servers in PROD environment. Each maintenace plnas have 8 tasks and each server has appx 10 databases.
I want to create maintenace plan using T-SQL and avoid using managment studio.
To summarise, i want a script to create maintenance plan (with 8 tasks).
Thanks.
Saurabh
October 14, 2008 at 1:07 pm
what are the tasks that you want to do?
October 14, 2008 at 1:55 pm
Let's say standard maintenace tasks like check integrity, reorganiza index and rebuild indexes.. etc..
All i want is one script whihc creates the maintenace plans with specific tasks. And i can rerun the same script on any other server to create exactly same mainetance plans.
October 14, 2008 at 2:19 pm
Create the first plan, script it out with Enterprise manager and then change the database name to $(DBName). Save the script to a file. Now create a batch file that sets the DBName environment variable to the database you want to run it for, and call the script using SQLCMD.
IE:
@echo off
if '%1' == '' goto usage
if '%1' == '/?' goto usage
if '%1' == '-?' goto usage
if '%1' == '?' goto usage
if '%1' == '/help' goto usage
SET DBName=Master
sqlcmd -S %1 -d msdb -E -b -i "MaintenanceTask.sql"
if %ERRORLEVEL% NEQ 0 goto errors
SET DBName=distribution
sqlcmd -S %1 -d msdb -E -b -i "MaintenanceTask.sql"
if %ERRORLEVEL% NEQ 0 goto errors
SET DBName=model
sqlcmd -S %1 -d msdb -E -b -i "MaintenanceTask.sql"
if %ERRORLEVEL% NEQ 0 goto errors
SET DBName=foo
sqlcmd -S %1 -d msdb -E -b -i "MaintenanceTask.sql"
if %ERRORLEVEL% NEQ 0 goto errors
goto finish
REM: How to use screen
:usage
echo.
echo Usage: MyScript ServerName
echo ServerName: the name of the target SQL Server
echo.
echo Example: MyScript.cmd ServerName
echo.
echo.
goto done
REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
Gary Johnson
Sr Database Engineer
October 14, 2008 at 2:48 pm
Now, I get to slide out from under the egg... I was thinking that you would use a regular SQL Job with steps created manually rather than the Maintenance Plan Package... So, what I posted will do apply a sql job that has steps outlined instead of using the package.
USE [msdb]
GO
/****** Object: Job [MaintainDB] Script Date: 10/14/2008 13:45:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/14/2008 13:45:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories
WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintainDB',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [BackupDB] Script Date: 10/14/2008 13:45:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BackupDB',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BACKUP DATABASE $(UserDB) TO disk=''C:\MSDB\BAK\$(UserDB).bak''',
@database_name=N'$(DBName)',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DBCC CheckDB] Script Date: 10/14/2008 13:45:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBCC CheckDB',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DBCC CHECKDB(''$(UserDB)'')',
@database_name=N'$(UserDB)',
@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_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Gary Johnson
Sr Database Engineer
October 15, 2008 at 11:54 am
How do i script out the maintenace plan using managment studio? I am using 2005
March 7, 2012 at 1:09 pm
To Script out a maintenance Plan
--I'm using SQL Server 2008 R2 but I think its the same in 2005.
Step 1. Of course create the maintenance plan using the wizard
>> Managment>Maintenance Plans right-click follow on screen instructs.
Step 2. After the plan is created run it at least once.
>> Right-click maintenancePlan (or whatever you named it) then click "Execute"
Step 3. Go to SQL Server Agent>Jobs folder right-click the MaintenancePlan then click
"script Job as" I would choose "DROP And CREATE to... New query editor window
Step 4. Review code on screen and make any necessary changes in DB names etc.
Say as .sql file
Thats all there is too it!!
Hope this helps
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply