Backup using DB Main. Plan
-
Received the following errors when trying to backup databases using a DB main. plan....
The DB main. plan reports....
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'E:\sqldb\sql_dmpdb\LienRelease021a\LienRelease021a_db_200405190723.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.
The job history reports....
Executed as user: JAXDOM\sqladmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
--------------------------------------------------
- I can manually backup the databases with no problems, just not using the DB main. plan
- I've confirmed that if I create a new database and restore the backups within the new database names the DB main will still fail.
- However, I've also confirmed the same databases can be moved to a different server and the db main. plans works perfectly....just to rule out a corrupt database.
- I've confirmed that I am using the correct service startup accout within the SQL Server Agent properties.
- I've confirmed the SQLSERVERAGENT under services had the correct 'log on' account and password.
- There is 36GB of free space, no space issues.
- The server is on the latest SQL service pack....Microsoft SQL Server 2000 - 8.00.847 (Intel X86) Jul 31 2003 16:59:33 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: )
- I've already re-booted the server
- The strange thing is that when running the DB main plan, some databases get backed up but others down, however, I've already proved I can take the same databases to different servers and the db main. plans works perfectly.
-
The databases that back up correctly using the maintenance plan, are they backed upt to the same location? Have you checked and confirmed that that directory structure exists?
-
I confirmed directory structure exists plus all databases are set to write to the same directory. The main. plan is also set to create sub-directories for each database.
Another test I tryed was to create a new main. plan with one backup that is successful and one that I knows that fails. The results were the same as the original main. plan, one failed and the other one was successful. As I had mentioned before the one that fails, I can move to a different server and the main. plan will be successful....strange
One thing I did notice which may not be relevant but the db that is successful is only 12MB, whereas, the one that fails is 1.3GB. I've backed up others in the past that were 10 & 20GB so that shouldn't matter, these 36GB free space. All system table are set to grow automatically and are on the same drive with 36GB free....Thanks
-
Are you able to use the maintenance plan to backup that database to a different drive on the same server?
-
There are two drives a C:\ and E:\. I changes the main. plan to write to the C:\drive, which has twice the space it needs. I received the same results. Only certain databases are failing. When I created the new main. plan I pretty much went with the defaults.
I'm going to rerun the SQL service pack to see if that has any affect tonight.
-
I had a similar issue in our company... Some plans worked while others failed.... What I found out is that our Network Service department had turned on Disk Quotas that were limiting ALL domain accounts to 1 gig space on the machine. I had to have them remove this Disk Quota from the account used by SQLServerAgent. Once they did backups worked as expected. You might have a similar problem.
Chad
-
Good point about disk quota's. Can you backup the failing database using TSQL to the same directory? If not, I would suspect the disk system (quotas, free space, etc). If it were a network share, there are some potential issues with larger databases.
-
OK, I've resolved the issue ...
The following is another error message displayed in the logs...
Processed <Number of Pages> pages for database '<databaseName>', file '<databaseName>' on file 1.100 percent backed up.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database '<databaseName>', file '<fileName>' on file 1.
This is referenced in Microsoft Knowledge Base Article -827452After speaking with Microsoft there seems to be an issue when you try to backup and restore a SQL Server databse on a pc running Windows 2003. I'm using SQL SErver 2000 by the way.A new dll was sent to me which resolve the issue. I forgot to ask the support person if it was going to be in the next Win 2003 service pack, I assume so...Thanks for you help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply