March 2, 2004 at 3:15 pm
Hi,
I am running SQL 7.0 with Sp4 on windows 2000 cluster. I have set up a maintenance plan to backup databases to local disk. Backup for one small database works fine but for other three databases which are 3.5,2.5 and 14 gig fail with error Backupdiskfile:openmedia backupdevice failed to open(os error = 2 file not found).
I have enough space in the drive and I checked the backup destination and it is correct.
My sqlserver service and SQL server agent are running with Domain accounts and also when I run backup database from query analyzer backup completes fine.
When I look at maintenance plan history the backup step is shown as sucessfull (time taken only 13 seconds)and verify backup step fails with filenot found error.
When I start the maintenance plan backup job manually I can see the backup file created with o kb and with in 5 seconds it disappers and the job fails.
For all other small databases master,MSDB backup completes just fine.
I never saw this behaviour before and I have many other servers where I run manintenance plan sucessfully.
I greatly appreciate any help in the regard .
thanks in advance.
Shyam
March 2, 2004 at 3:27 pm
It could be the security permission for the job. Set the user to sa. See the article below.
PRB: Unable to Back Up Database to a Network Drive Without Permissions (207187)
This article was previously published under Q207187
SYMPTOMS
When you attempt to back up a database to a network drive, you may receive one of the following error messages, depending on the version of SQL Server you are running.
On SQL Server version 7.0 and 2000
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'E:\pubs.dat'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
The following information will be in the error log:
998-12-29 11:58:21.62 kernel BackupDiskFile::CreateMedia: Backup device 'E:\pubs.dat' failed to create. Operating system error = 5(Access is denied.).
On SQL Server version 6.5
[SQL Server] Can't open dump device '<dump device path>'. Device error or device off line. Please consult the SQLServer errorlog for more details.
Messages similar to the following appear in the error log:
97/11/25 11:25:49.54 kernel dbsvolopen: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)
97/11/25 11:25:51.60 kernel dbswritecheck: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)
The following message may also appear in the error log:
Msg 18204: dbswritecheck: Backup device '<device path and file>' failed to open, operating system error = 5 (Access is denied.)
CAUSE
This error message usually occurs as a result of incorrect permissions to the network drive.
WORKAROUND
To enable backups to network drives, perform the following steps:
The SQL Server service must be started using a domain user account to access any resources on a remote computer. Verify that the MSSQLServer service is started under a domain account that has write access to both the Windows NT Server share and its underlying partition (if the partition is formatted with the Windows NT file system, or NTFS).
In Control Panel, double-click the Services icon.
Select the MSSQLServer service and then click Startup.
Examine the startup options for the service and verify that This Account is selected and that a valid domain account is supplied in the form Domain_Name\Domain_account (with the correct password).
NOTE: If you changed the service to run under a domain account, you must stop and restart the MSSQLServer service.
Verify that the account specified has write access on the Windows NT share to which you are backing up, as well as the underlying partition (if the partition is formatted as NTFS). To do this, perform the following steps:
Right-click the share name in Windows NT Explorer.
Click Sharing on the shortcut menu.
On the Security tab, click Permissions.
March 2, 2004 at 3:31 pm
Thanks a lot for your response.
I have the owner of the job set to SA and also both my SQL Server Service and SQL server agent run under domian admin account. Also I am trying to back it up to the local drive. One of the databases (small one)completes fine.
thanks
Shyam
March 3, 2004 at 2:37 am
Have you looked in the Maintenance Plan report file. This gives far more diagnostic information than found in the Job History display.
Also, have you tried manually copying a file that is a similar size to your large backups to your backup destination. If this works, try again when you are logged on using the SQL Server service account. Presumably you have checked there is enough free space on your destination device to hold your backup.
One possible reason why you can create small files but not big files is NTFS disk quota restrictions. It may be worth checking with your administrator.
All information provided is a personal opinion which may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2004 at 3:29 am
I usually avoid maint plans as they can prove prone to sudden failure - have you tried scheduling a std tsql backup job ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 3, 2004 at 4:56 am
Also try running the backup to some other device say some other server on LAN.
Cheers!
Arvind
March 3, 2004 at 5:57 pm
thanks for all your responses.
I can copy file of same size to the backup directory. I checked the maintenance plan report and did'nt get anythign more.
I even mapped a netowrk drive and can backup the small database but not the big ones.
It is really confusing. I started the profiler and ran the maintenance plan job.
The mysterious thing is I could see all SQL statments like getting plan details and database details from msdb but not the actual backup database statement .
It starts like getting all details from msdb tables and then inserts a record into sysdbmaintplan_history with sucesses status and next step is Restore verify only and it will fail as there is no backup file to verify.
I set maintenance plan to backup transaction logs for these databases and they are sucessfull and also in profiler I can see backup log statement for these jobs.
This thing is really weird.
I greatly appreciate any suggesstions.
thanks
Shyam
February 10, 2005 at 3:46 pm
You had this problem a year ago SQL 7 on Win 2000 Cluster
One of our divisions is having a similar problem but running SQL 2000 on a windows 2003 cluster.
Did you ever figure out the cause?
The maintenance plan reports no errors on the backup (which is to the backup directory on the same clustered drive as the MS SQL Data).
However it fails in the Verify step (file not found)
If I turn off the verify, there is no error at all, but still no backup file.
Bob Sturnfield
sqlservercentral@sturnfield.com
Bob Sturnfield
February 27, 2005 at 5:55 am
I did figure this out. The maintenance plan was hiding the error.
This command failed from the maintenance plan:
BACKUP DATABASE [OurDatabase] TO DISK = N's:\mssql\MSSQL$OurInstance\BACKUP\OurDatabase_db_200502242130.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
But ran fine from Query Analyser on my laptop:
10 percent backed up.
20 percent backed up.
30 percent backed up.
40 percent backed up.
50 percent backed up.
60 percent backed up.
70 percent backed up.
80 percent backed up.
90 percent backed up.
Processed 2758312 pages for database 'OurDatabase', file 'OurDatabase_Data' on file 1.
100 percent backed up.
Processed 1 pages for database 'OurDatabase', file 'OurDatabase_Log' on file 1.
BACKUP DATABASE successfully processed 2758313 pages in 632.656 seconds (35.716 MB/sec).
Running as a SQLAgent Job it failed (run as sa):
Job 'BACKUP DATABASE OurDatabase' : Step 1, 'BACKUP DATABASE OurDatabase' : Began Executing 2005-02-24 22:26:03
ConnectionRead (WrapperRead()). [SQLSTATE 01000]
Msg 11, Sev 16: General network error. Check your network documentation. [SQLSTATE 08S01]
Msg 3211, Sev 16: 10 percent backed up. [SQLSTATE 01000]
It also fails when run from Query Analyser on OurServer:
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
This time the library is identified as "Named Pipes"
Checking Cliconfg, we find the the definition created to do the install were never removed after SP3A was installed:
I am renaming these aliases . . .
Trying it again from Query Analyser on OurServer:
10 percent backed up.
20 percent backed up.
30 percent backed up.
40 percent backed up.
50 percent backed up.
60 percent backed up.
70 percent backed up.
80 percent backed up.
90 percent backed up.
Processed 2758312 pages for database 'OurDatabase', file 'OurDatabase_Data' on file 1.
100 percent backed up.
Processed 1 pages for database 'OurDatabase', file 'OurDatabase_Log' on file 1.
BACKUP DATABASE successfully processed 2758313 pages in 632.656 seconds (35.716 MB/sec).
The SQLAgent Job also ran fine:
Job 'BACKUP DATABASE OurDatabase' : Step 1, 'BACKUP DATABASE OurDatabase' : Began Executing 2005-02-24 23:38:39
10 percent backed up. [SQLSTATE 01000]
20 percent backed up. [SQLSTATE 01000]
30 percent backed up. [SQLSTATE 01000]
40 percent backed up. [SQLSTATE 01000]
50 percent backed up. [SQLSTATE 01000]
60 percent backed up. [SQLSTATE 01000]
70 percent backed up. [SQLSTATE 01000]
80 percent backed up. [SQLSTATE 01000]
90 percent backed up. [SQLSTATE 01000]
Processed 2758312 pages for database 'OurDatabase', file 'OurDatabase_Data' on file 1. [SQLSTATE 01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'OurDatabase', file 'OurDatabase_Log' on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 2758313 pages in 659.139 seconds (34.281 MB/sec). [SQLSTATE 01000]
I started the Maintenance Plan 5 job, and it now runs fine.
I am sure this has confirmed what the problem was:
The version on c:\Windows\System32\dbnmpntw.dll is 2000.85.1022.0 (MDAC 2.8) which has known bugs in named pipes. I would suggest swapping in 2000.85.1022.42,
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452&Product=sql2k
Bob Sturnfield
September 9, 2005 at 4:25 am
Hi there,
Can you let me know where I can get version 2000.85.1022.42 of dbnmpntw.dll from please....many thanks?
Vin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply