Error in backup process.

Hi everybody,

I'm trying to backup a database of 330 GB more or less from my server to an external hard disk connected by optical fiber and I'm receiving the next error:

[SQLSTATE 01000] (Message 3211) Write on "H:\sqlbackup\pm_import" failed: 33(The process cannot access the file because another process has locked a portion of the file.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea?

Thanks!

refer this link,

http://msdn2.microsoft.com/en-us/library/ms837537.aspx is there any other process that is accessing your db just type sp_who2 and see if anything is accessing your db.......any operations going in your db other than backup any bulk insert etc?......

|||Absolutely nothing! I put the batabase in SINGLE_USER mode.

|||

no need to bring to Single user mode . SQL Server has online backup architecture. The problem seems to be the Target file. ie the Backup file in H:\SQLBackup. check any process is using the file when u take backup. It need exclusive access to that file. to debug the issue... create a new database and take backup in the same path ie . H:\sqlbackup\test.bak or something like this. if u r getting same error then the problem may be something else. post back the result of this debug process i mentioned

Madhu

|||

Thank you very much Madhu. I thought the data file was locked by another process, but in fact it was the target file who was block by the anti-virus.

So problem solved!

Thanks again.

Error handling when calling external sql-Files

I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaulHi

Look at using the -b flag when you call osql, there is more information in
Books Online.

John

<lvpaul@.gmx.net> wrote in message
news:1128067412.114048.12840@.g44g2000cwa.googlegro ups.com...
I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaul|||Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul|||Hi

You may want to try using EXEC for your select statement

declare @.errorcode int
EXEC ('select * from abc')
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

John

<lvpaul@.gmx.net> wrote in message
news:1128491173.083475.231000@.o13g2000cwo.googlegr oups.com...
Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul