Backup Failing - SQL Server Forums

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 2005 Forums

High Availability (2005)

Backup Failing

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2007-02-26 : 06:10:20

Hi everybody,

I have the below script on SQL Server 2005, SP1 on Windows 2003 Server to backup a database

BACKUP DATABASE [XYZ] FILEGROUP = N'PRIMARY' TO [XYZ] WITH NOFORMAT, NOINIT, NAME = N'XYZ-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'XYZ' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'XYZ' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''XYZ'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [XYZ] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

And after 40% processing the backup fails with below error message. I have been trying to find out the solution for past 2 days, but in vain. Can someone look at below results and show me any pointers?
10 percent processed.
20 percent processed.
30 percent processed.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'CreateFile' on 'F:\MSSQL\005.YINT01\XYZ\XYZ01.mdf'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 50000, Level 16, State 1, Line 3
Verify failed. Backup information for database 'XYZ' not found.
Msg 3250, Level 16, State 0, Line 4
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Line 4
VERIFY DATABASE is terminating abnormally.

Thanks in advance!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

kfarlee
Microsoft SQL Server Product Team

9 Posts

Posted - 2007-02-27 : 13:27:39

It looks like you're trying to back up a database to itself instead of a separate backup file.
What specifically is in the TO [XYZ] at runtime?
What happens if you substitute TO DISK= N'F:\MSSQL\005.Yint01\XYZ\MyXYZBackup.bak' ?

Kevin Farlee
SQL Server Storage Engine PM

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2007-02-27 : 21:31:33

Kevin,

I backup the database thro' backup device. Let me replace the placeholders XYZ to db name EDGE. Here is the script of the backup device

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'EDGE', @physicalname = N'D:\FT005.YINT01.USERDB BACKUP\EDGE\EDGE.BAK'.

The path of the .mdf files are in the path F:\MSSQL\005.YINT01\EDGE\. Infact I tried running the backup directly to the .BAK file instead of using backup device. I still had the same error. I would want to add more details on this posting

I have full-text indexing on this database, an AV software configured and the I have run the above backup script with both the services stopped or disabled. I haven't had any success. Moreover, I don't have any tape backup device attached to the server.

I went a bit further, to see what exactly is happening during the backup script run.

I ran the process monitor and found this entry nearest to the failure

CreateFile F:\MSSQL\005.YINT01\EDGE\EDGEACTIVITYCENTER01.mdf SHARING VIOLATION Access:
Generic Read, Disposition: Open, Options: No Buffering, Non-Directory File,
Open No Recall, Attributes: n/a, ShareMode: Read, Write, AllocationSize: n/a

I am unable to understand why should a CreateFile should be run before the .BAK file is written to the defined path (D:\). Infact I assumed that RESTORE VERIFYONLY, may be the culprit, and I had removed that command too. I didn't see any success

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-02-28 : 13:25:48

after creating the device, do you see it under sysdevices (or sql2005 equivalent table)?
if yes...
run a simple backup to the device
do you get the same error?
if yes... post both the commands for device creation and backup(the ones you used above)

hth

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources