SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Backup Issue


Database Backup Issue

Author
Message
Ronnie Jones
Ronnie Jones
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1597 Visits: 315
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
Ray Mond
Ray Mond
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3871 Visits: 1544
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).

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Ronnie Jones
Ronnie Jones
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1597 Visits: 315
Thanks Ray!
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138089 Visits: 18218
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" ;-)
Ronnie Jones
Ronnie Jones
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1597 Visits: 315
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138089 Visits: 18218
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" ;-)
Chuck Hottle
Chuck Hottle
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 1227
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.
Ronnie Jones
Ronnie Jones
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1597 Visits: 315
Thanks Chuck! I never thought about that. I'll give that a try and let you know.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)SSC Guru (512K reputation)

Group: General Forum Members
Points: 512068 Visits: 44309


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
arnipetursson
arnipetursson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3605 Visits: 1019
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search