Competition: What’s your favorite Redgate tool? Enter now.

Back up errors

gsc_ghxgsc_ghx Posts: 73
edited May 14, 2008 10:09AM in SQL Backup Previous Versions
I am getting the following error after a backup:
SQL Backup log file
5/9/2008 5:46:12 PM: Backing up CAT_StJoseph_Regional_Health (full database) to:
\\prdsmb1.ghx.com\sqlbkp\CATSQL02\sqlbkp\CAT_Regional_Health_FULL_RG.bak

5/9/2008 5:46:12 PM: BACKUP DATABASE [CAT_Regional_Health] TO DISK = '\\prdsmb1.ghx.com\sqlbkp\CATSQL02\sqlbkp\CAT_Regional_Health_FULL_RG.bak' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 1

5/9/2008 6:17:34 PM: Error: Cannot open file "\\prdsmb1.ghx.com\sqlbkp\CATSQL02\sqlbkp\CAT_Regional_Health_FULL_RG.bak". The process cannot access the file because it is being used by another process.

There is nothing to my knowledge that i can find that has a lock on this file.
My sQL Server log is showing me:
Date 5/9/2008 5:46:30 PM
Log SQL Server (Current - 5/9/2008 7:13:00 PM)

Source backup

Message
Database backed up: Database: CAT_Regional_Health, creation date(time): 2007/05/11(13:43:35), pages dumped: 94696, first LSN: 2307:6447:1, last LSN: 2307:6451:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_59836AB9-2330-4F48-9473-7BED90701861'}).

I have moved my full backup files to a new directory - one that i created today. This job still failed.

could there be a delay in backing up this db and it is failing on the delay?

Any suggestions will be greatly appreciated. I am running out of ideas as to what is causing this issue. I have changed the time of the job to start before midnite, right after midnite, at 6am. I have manually started the job and it still fails... Help! :(

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the entire contents of the log file? Also, what version of SQL Backup are you using?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter - this is the entire log file for this process. We are using Redgate Sql Backup 4.2.0.511.

    geri
  • peteypetey Posts: 2,358 New member
    SQL Backup creates the backup file in 2 stages, once to write the compressed data and another to update the file header.

    It appears that it failed to update the header information, that the file was used by another process immediately after SQL Backup created it.

    Could you please test if the database can be restored using the file that was created? Could you also pls run the following:

    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [\\prdsmb1.ghx.com\sqlbkp\CATSQL02\sqlbkp\CAT_Regional_Health_FULL_RG.bak] " '

    and compare the output with that from another file that was created without errors? Are the LSN details missing?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter- yes the LSN details are missing. That was informative. My only question - how do i find what might be holding that file and causing this to error? I have moved this backup out of the drive tape backup so that is not an issue. I do not have my logs going to this drive location - the only thing would be this backup. It was working fine until about 2 weeks ago. Nothing has changed on this clustered server that i know of. We backup to a file sharepoint drive. I am not having this issue with any other backup.

    Any help is great.
    g
  • peteypetey Posts: 2,358 New member
    SQL Backup isn't able to identify which process is holding a lock on the file. The most common cause we've encountered has been anti-virus applications.

    Is this happenning for every backup? Do you also have a list of system updates/patches/hot fixes that were applied prior to the error occurring?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I will check with my Unix admins today. My history shows i have not had a successful backup prior to 4/24/08. If I manually run the procedure to backup - database by database - it will run. I have 36 client databases that are being affected.

    Thank you...

    Geri
Sign In or Register to comment.