Overwrite a database when i manually run a 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 2000 Forums

SQL Server Administration (2000)

Overwrite a database when i manually run a job

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2012-06-06 : 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

38200 Posts

Posted - 2012-06-06 : 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

47 Posts

Posted - 2012-06-06 : 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

38200 Posts

Posted - 2012-06-06 : 17:31:57

You need to add WITH REPLACE to the restore command.

RESTORE DATABASE Test
FROM DISK = 'E:\Backup\Test.bak'
WITH REPLACE

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jayram
Starting Member

47 Posts

Posted - 2012-06-06 : 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

38200 Posts

Posted - 2012-06-06 : 18:13:29

Okay, you can do it all in one command though using WITH REPLACE. It saves you time this way as the database files can be reused. With drop/restore, it'll have to create those files again, which means unnecessary IO time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources