Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Backup Issue Expand / Collapse
Author
Message
Posted Thursday, August 18, 2011 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:43 PM
Points: 140, Visits: 224
Hello All,

I'm running SQL 2008 R2. I have an intermediate issue with my backup for this one database. Some days the backup completes successfully with no problem and other days - could be 3 or 4 in a row - the backup fails with the following error:

Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SMS.BAK'. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The command used to backup the database is:

BACKUP DATABASE [SMS] TO DISK = N'F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SMS.BAK' WITH NOFORMAT, INIT, NAME = N'SMS- Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Is there any way to determine what that process is that is preventing the backup from occurring? Or are there any suggestions on how I can ensure that the backup device isn't causing an issue with the backup?

Let me know if you have any questions.

Thanks in advance for your help.

Ronnie
Post #1161873
Posted Thursday, August 18, 2011 8:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 2:00 AM
Points: 343, Visits: 1,517
You could use Process Explorer from Sysinternals to find out which application is using that backup file. Or you could use the command line application 'handle.exe' (also from Sysinternals).

Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #1162211
Posted Monday, August 22, 2011 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:43 PM
Points: 140, Visits: 224
Thanks Ray!
Post #1163291
Posted Monday, August 22, 2011 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
do you have any Windows server level backup jobs running at all (BackupExec, etc)?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1163324
Posted Monday, August 22, 2011 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:43 PM
Points: 140, Visits: 224
Yes, there is a system level backup but I'm not sure what time it starts. I'll have to check on that. But we're not running any agents that backup the hot .mdf file which means that the database and log files should be skipped. Unless you're saying that I wouldn't be able to write to the backup folder. I know that the .bak file does get backed up by the system backup.

I'll verify that info.
Post #1163582
Posted Monday, August 22, 2011 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
Ronnie Jones (8/22/2011)
Yes, there is a system level backup but I'm not sure what time it starts. I'll have to check on that. But we're not running any agents that backup the hot .mdf file which means that the database and log files should be skipped. Unless you're saying that I wouldn't be able to write to the backup folder. I know that the .bak file does get backed up by the system backup.

I'll verify that info.


The error defines it cannot open the sql server backup file, possibly because the system level backup has a handle on the file to back it up to tape


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1163610
Posted Monday, August 22, 2011 2:52 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:31 AM
Points: 198, Visits: 1,127
We have this issue because of Commvault system backups that run at odd times. I added 2-3 retries to the backup job step with 15-30 minute retry increments. That resolved a lot of the failures.
Post #1163629
Posted Tuesday, August 23, 2011 10:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:43 PM
Points: 140, Visits: 224
Thanks Chuck! I never thought about that. I'll give that a try and let you know.
Post #1164141
Posted Monday, March 4, 2013 9:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 36,793, Visits: 31,252


You are a hairball. All you've done is make an illegal copy of Microsoft Documentation at the following URL.

http://support.microsoft.com/kb/207187

I wonder how your sponsors will feel about finding out that you're nothing but a knock-off artist.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426583
Posted Tuesday, March 5, 2013 5:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 258, Visits: 813
We had this issue on old backup processes due to system backups.

The simple fix was to back up to a file name with a date and timestamp.
Post #1427098
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse