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: 2 days ago @ 8:42 AM
Points: 140, Visits: 217
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: Tuesday, January 14, 2014 11:32 PM
Points: 343, Visits: 1,515
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

TLogInfo - the only FREE tool to analyse your transaction logs. Download here.
SQL BAK Reader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here.
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: 2 days ago @ 8:42 AM
Points: 140, Visits: 217
Thanks Ray!
Post #1163291
Posted Monday, August 22, 2011 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
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: 2 days ago @ 8:42 AM
Points: 140, Visits: 217
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:26 PM
Points: 5,961, Visits: 12,847
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: Tuesday, April 15, 2014 3:04 PM
Points: 198, Visits: 1,116
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: 2 days ago @ 8:42 AM
Points: 140, Visits: 217
Thanks Chuck! I never thought about that. I'll give that a try and let you know.
Post #1164141
Posted Monday, March 04, 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 @ 1:13 PM
Points: 35,969, Visits: 30,261


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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 05, 2013 5:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:20 PM
Points: 237, Visits: 763
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