Database Backup Issue

Question

Database Backup Issue

Ronnie Jones

Hall of Fame

Points: 3377
More actions
August 18, 2011 at 9:39 am

#243357

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

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 Hall of Fame Points: 3377 More actions · Answer 2

Ronnie Jones

Hall of Fame

Points: 3377

August 22, 2011 at 7:49 am

#1371410

Thanks Ray!

Perry Whittle SSC Guru Points: 233846 More actions · Answer 3

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 Hall of Fame Points: 3377 More actions · Answer 4

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 SSC Guru Points: 233846 More actions · Answer 5

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 SSCarpal Tunnel Points: 4777 More actions · Answer 6

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 Hall of Fame Points: 3377 More actions · Answer 7

Thanks Chuck! I never thought about that. I'll give that a try and let you know.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

shijobaby (3/4/2013)
http://answerforu.com/2011/09/26/server-msg-3201-level-16-state-1-line-1-cannot-open-backup-device-epubs-dat-device-error-or-device-off-line-see-the-sql-server-error-log-for-more-details-server-msg-3013-level-16-stat/[/url]

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

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.

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 10

here i am also facing the same problem.

here we have a job with 2 steps here first step it will take the complete database backup. and second step also take the complete backup of another database.

in my case first step is working properly and second one is not working and through the above error.

here drives are in same system.

Waqqas Khokhar Grasshopper Points: 24 More actions · Answer 11

One common reason that people may not immediately notice:

In a Windows Server environment, check if your destination backup location has Shadow Copies enabled on the specific drive.