We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.
My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.
Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.
When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.
Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.
Just in case anyone else hits this issue:
The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.
Follow on question:
THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.
without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.
It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.
Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment