| Author |
 Topic  |
|
|
jayram
Starting Member
27 Posts |
 Posted - 06/06/2012 : 16:57:30
|
hi
i have a SQL Server Agent Job that makes a backup of a database once every weekday at a certain time. the backed up database appears on the Serve - SSMS with name of the database appended to the date so that it has a unique database name. if i run the job manually, it will fail because there is already a database created for that day.
Is there an option to Overwrite the database when i manually run the Job?
is there an argument in the sp_add_jobschedule that would let me do it?
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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'MakeBackup',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Execute package: MakeBackup',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Server\Administrator',
@notify_email_operator_name=N'XXXXX', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MakeBackup',
@step_id=1,
@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'CmdExec',
@command=N'DTSRun /~Z0xEDFDEE03F0A00681F68712760F1033B4595C6BF73FD84CE540E510E2230B11B66116F57084E842CEB5AC2768AA6BEC662464EC4B10E0D419F3A60202F03AC80EC1392C70DA6D47EF3342FB4BD2EF7AD7FB3F4BD48913C9EF4AF78C ',
@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'MakeBackup',
@enabled=0,
@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=20100311,
@active_end_date=99991231,
@active_start_time=174432,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MakeBackup_Mon-Fri',
@enabled=1,
@freq_type=8,
@freq_interval=62,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100317,
@active_end_date=99991231,
@active_start_time=30000,
@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
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/06/2012 : 17:16:23
|
I don't understand your problem.
Overwrite the database? Do you mean overwrite the backup file? Or are you restoring that file? Your post doesn't make sense.
No there's no option for sp_add_jobschedule, sp_add_jobstep or anything. You would fix the source program, which appears to be a DTS package. If you are doing a restore, you would specify REPLACE. If you are doing a backup, you would specify INIT.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
 |
|
|
jayram
Starting Member
27 Posts |
Posted - 06/06/2012 : 17:29:08
|
Thanks for looking, Tara.
The job creates a back up file and restores that file to a new database.
e.g: the database is Adventures
the job creates a backup of Adventures and restores the backed up database as new database on the server like Adventures_060612 and then tomorrow it does Adventures_060712. The job runs at 3 am. If i run the job manually, lets say today anytime after 3 am, it fails to run cos there already is a database created for that day - Adventures_060612.
But what i want is, if i run it manually, lets say today, it should overwrite Adventures_060612 that was created at 3 am with the one i just created by running the job.
currently what i do is drop the database manually by running a simple script and start the job.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
jayram
Starting Member
27 Posts |
Posted - 06/06/2012 : 18:08:52
|
Thanks Tara
i figured out that the DTS package has an ActiveX script which creates the database and i have altered it to check if exists database then drop and create database. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
|
|
Overwrite a database when i manually run a job
