July 23, 2003 at 11:57 pm
I have created tow Diffrent jobs
One Copy a Backup from the LAN and restores it , The Other Backup a DB and copies it to the LAN
Step 5 of the jobs contain the RESTORE operation
The Transact SQL command is beeing run successfuly By Query Analyzer
When the job runs the step starts and then reports a failure
5äîòøëú äúééçñä àì áéèåì ëàì freestmt/close [SQLSTATE 501S0] (Error 0) Processed 17704 pages for database 'HIB', file 'HIB_For_Arad_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 2 pages for database 'HIB', file 'HIB_For_Arad_Log' on file 1. [SQLSTATE 01000] (Message 4035) Backup or restore operation successfully processed 17706 pages in 11.188 seconds (12.964 MB/sec). [SQLSTATE 01000] (Message 3014). The step failed.
Checking The Logs Both of the NT and SQL and testing the restore operation by deleteing all tables from the DB Before restoring
sujest that the restor operation is a success
I have check to see what report the step should do when it successeeds and it is all right.
I have another server where the Job runs Fine.
I have the same error result when trying to Backup on the second JOB.
I am adding the First JOB Here (as scripted from the server)
-- Script generated on 24/07/2003 08:42
-- By: sa
-- Server: ARAD1
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Copy And Restore HIB')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Copy And Restore HIB'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Copy And Restore HIB'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Copy And Restore HIB', @owner_login_name = N'DOM01\Administrator', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @notify_email_operator_name = N'Data Base Administrator'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Delete Old RaR File', @command = N'Del D:\MSSQL7\Backup\HIB.rar
', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Delete Last BAK File', @command = N'Del D:\MSSQL7\Backup\HIB_FOR_ARAD.BAK', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Copy HIB Rar From Srv10', @command = N'Copy \\Srv10\d$\MSSQL7\Backup\ARAD\HIB.rar D:\MSSQL7\Backup\*.*', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @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
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Extrarct Rar File', @command = N'D:\MSSQL7\BACKUP\Winrar.exe e D:\MSSQL7\BACKUP\HIB.rar D:\MSSQL7\BACKUP\', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @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
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'Restore HIB', @command = N'Use master
Restore DATABASE HIB
FROM DISK =''D:\MSSQL7\BACKUP\HIB_FOR_ARAD.BAK''
WITH
Replace,
MOVE ''HIB_FOR_ARAD_Data'' TO ''D:\MSSQL7\DATA\HIB_Data.mdf'',
MOVE ''HIB_FOR_ARAD_Log'' TO ''D:\MSSQL7\DATA\HIB_Log.ldf''
', @database_name = N'master', @server = N'', @database_user_name = N'dbo', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @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
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 6, @step_name = N'Refresh Cust_Tel', @command = N'set nocount on
CREATE TABLE #Tel (
[TelNo] [char] (12) NOT NULL ,
[CID] [int] NOT NULL
)
insert into #Tel
select ltrim(rtrim(CU_Customer.PrefixTelHome))+''-''+ltrim(rtrim(CU_Customer.TeLHome)),CID from CU_Customer
where (PrefixTelHome is not NULL) And (TeLHome is not null)and
len(ltrim(rtrim(CU_Customer.PrefixTelHome))+''-''+ltrim(rtrim(CU_Customer.TeLHome)))<=12
insert into #Tel
select ltrim(rtrim(PrefixTel2))+''-''+ltrim(rtrim(TeL2)) ,CID from Cu_Customer
where (PrefixTel2 is not NULL) And (TeL2 is not null)and
len(ltrim(rtrim(CU_Customer.PrefixTel2))+''-''+ltrim(rtrim(CU_Customer.TeL2)))<=12
insert into #Tel
select ltrim(rtrim(PrefixTelFax))+''-''+ltrim(rtrim(TeLFax)) ,CID from Cu_Customer
where (PrefixTelFax is not NULL) And (TeLFax is not null)and
len(ltrim(rtrim(CU_Customer.PrefixTelfax))+''-''+ltrim(rtrim(CU_Customer.TeLfax)))<=12
insert into HIB.dbo.CU_CustTel
select Distinct TelNo,CID,0 from #Tel
where (#Tel.TelNo is not null) and (LTRIM(RTRIM(#Tel.TelNo)) <> ''-'') and (left(ltrim(rtrim(#Tel.TelNo)),1)=''0'')
and TelNo not in(select TelNo From HIB.dbo.CU_CustTel )
Delete
FROM HIB..CU_CustTel
where
(len(tel)<>10) or
(left(Tel,2) not in(''05'',''06'') and CHARINDEX (''-'',TEL,1)<>3) or
(CHARINDEX (''-'',TEL,1)=0) or
(CHARINDEX (''/'',TEL,1)>0) or
(CHARINDEX (''*'',tel,1)>0) or
(CHARINDEX (''!'',tel,1)>0) or
(CHARINDEX (''@'',tel,1)>0) or
(CHARINDEX (''#'',tel,1)>0) or
(CHARINDEX (''$'',tel,1)>0) or
(CHARINDEX (''%'',tel,1)>0) or
(CHARINDEX (''&'',tel,1)>0)or
(left(,2) not in (''02'',''03'',''04'',''05'',''06'',''07'',''08'',''09''))
Update HIB.dbo.CU_CustTel set PolType=PolType+1
From HIB.dbo.CU_CustTel Inner Join HIB.dbo.P_Policy
on HIB.dbo.CU_CustTel.Cid =HIB.dbo.P_Policy.CID
Update HIB.dbo.CU_CustTel set PolType=PolType+2
From HIB.dbo.CU_CustTel Inner Join HIB.dbo.BR_Policy
on HIB.dbo.CU_CustTel.Cid =HIB.dbo.BR_Policy.CIDBR
where (HIB.dbo.CU_CustTel.PolType & 2)=0
', @database_name = N'HIB_TLM', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @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
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily Run', @enabled = 1, @freq_type = 4, @active_start_date = 20030702, @active_start_time = 50000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
July 24, 2003 at 12:47 pm
Have you checked that the db is not in use when the job runs? If there is any user in the db, it will not restore.
Steve Jones
July 26, 2003 at 11:54 pm
quote:
Have you checked that the db is not in use when the job runs? If there is any user in the db, it will not restore.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
July 29, 2003 at 10:50 am
Hi ,
The error appears that you have any user login into database.
Try tomodification the job to verify is exists users login in own database.
If exists and it´s possible to kill them, try.
Let me know if this suggest correct yout problem
Hildevan
Hildevan O Bezerra
Hildevan O Bezerra
July 29, 2003 at 11:35 pm
This Eror Was Solve By Reinstalling to OS
NT Hebrew Anbled with No MDAC
The Hebrew MDAC Has returned an Un Kown Answer to the Sql server
I could not un istalled it so ReInstalled the Entire Server , Luckly For Me it was on a build up stage
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply