<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Database Backup Issue
Database Backup Issue
Rate Topic
Display Mode
Topic Options
Author
Message
Ronnie Jones
Ronnie Jones
Posted Thursday, August 18, 2011 9:39 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:09 PM
Points: 136,
Visits: 187
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
Ray Mond
Ray Mond
Posted Thursday, August 18, 2011 8:12 PM
Old Hand
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:28 AM
Points: 343,
Visits: 1,510
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
Ronnie Jones
Ronnie Jones
Posted Monday, August 22, 2011 7:49 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:09 PM
Points: 136,
Visits: 187
Thanks Ray!
Post #1163291
Perry Whittle
Perry Whittle
Posted Monday, August 22, 2011 8:25 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 5,204,
Visits: 11,163
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
Ronnie Jones
Ronnie Jones
Posted Monday, August 22, 2011 1:14 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:09 PM
Points: 136,
Visits: 187
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
Perry Whittle
Perry Whittle
Posted Monday, August 22, 2011 2:02 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 5,204,
Visits: 11,163
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
Chuck Hottle
Chuck Hottle
Posted Monday, August 22, 2011 2:52 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: 2 days ago @ 1:35 PM
Points: 197,
Visits: 1,068
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
Ronnie Jones
Ronnie Jones
Posted Tuesday, August 23, 2011 10:11 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 10, 2013 4:09 PM
Points: 136,
Visits: 187
Thanks Chuck! I never thought about that. I'll give that a try and let you know.
Post #1164141
Jeff Moden
Jeff Moden
Posted Monday, March 04, 2013 9:30 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923,
Visits: 26,811
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/
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1426583
arnipetursson
arnipetursson
Posted Tuesday, March 05, 2013 5:51 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 137,
Visits: 420
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
