-- Help with pass in para to SQL Job.

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

SQL Server 2008 Forums

SQL Server Administration (2008)

-- Help with pass in para to SQL Job.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-07 : 12:30:01

[code]Hi,

Is there a way to pass in the backup path to script. I have an application calling this script and let the user select which drive or
backup path then pass the path to script below. This need to execute about 1500 clients. I know it's easy to just open the Query Analyzer
and hit F5. This is a completely different requirements. Any suggestions would greatly appreciate.

Please see the testing... below.

Thank you.

----------------------------------------------------------------------
USE [msdb]
GO
-- Delete the job if exists.
DECLARE @jobId BINARY(16)
SET @jobId = ( SELECT job_id
FROM msdb.dbo.sysjobs_view
WHERE ( name = N'#Admin Nightly Backup (DIFF) client DBs @ 1:00 A.M.' )
)
-- PRINT @jobId
BEGIN
IF (@jobId IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId;
END
END;
GO

-----------------------------------------------------------------------------------------
--Testing....

DECLARE @MyBackupPath VARCHAR(65)
SET @MyBackupPath = 'c:\MSSQL\Backup\Test\'

--SELECT REPLACE('c:\', 'c:\', 'c:\MSSQL\Backup\Test\')

------------------------------------------------------------------------------------------

/****** Object: Job [#Admin Nightly Backup (DIFF) client DBs @ 1:00 A.M.] Script Date: 03/06/2012 15:16:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/06/2012 15:16:14 ******/
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'#Admin Nightly Backup (DIFF) client DBs @ 1:00 A.M.',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This is a differential backup nightly at 11:00 P.M.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [#1 DIFF Backup of all DBs.] Script Date: 03/06/2012 15:16:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'#1 DIFF Backup of all DBs.',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

-- Need to pass in the backup path here.

--DECLARE @MyBackupPath VARCHAR(65)
--SET @MyBackupPath = ''c:\MSSQL\Backup\Test\''

EXECute msdb.dbo.usp_AdminDiffBackupDBs @backupdir = @MyBackupPath;
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [#2 Shrink the log files.] Script Date: 03/06/2012 15:16:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'#2 Shrink the log files.',
@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' USE PHARMACY;
GO

DBCC SHRINKFILE (Pharmacy_log, 2000);
GO

USE POS;
GO
DBCC SHRINKFILE (POS_log, 2000);
GO

USE RXIMAGES;
GO
DBCC SHRINKFILE (RxImages_log, 2000);
GO',
@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'#2 Diff Backup starting @ 1:00 A.M. daily.',
@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=20120301,
@active_end_date=99991231,
@active_start_time=10000,
@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
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

-- EXECute msdb.dbo.sp_start_job @job_name = [#Admin Nightly Backup (DIFF) client DBs @ 1:00 A.M.], @server_name = @@SERVERNAME;[/code]

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 13:27:48

What do you mean by..."pass in"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 23:40:10

why not use maintenance task for this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources