| Author |
Topic |
|
ras2a
Yak Posting Veteran
66 Posts |
 Posted - 2006-06-22 : 08:50:45
|
| I have had several database errors (reported in event log) on our main database this morning. I assume this is due to faulty hardware etc (that's 'usually' the case, right?). Anyway, I have a maintenance plan in place that runs the 'DBCC CHECKDB ([ourdatabase])' command. However, this command constantly reported no problems with the database (0 errors).1) Is the 'DBCC CHECKDB' command 'comprehensive' enough to find ANY/ALL errors in databases?2) How do you run a repair on a suspected corrupted DB? (I have tried the REBUILD_REPAIR command but it fails...saying something about incorrect syntax (as if it's not a recognised command in QA)3) I currently use Veritas Backup Exec's 'SQL Agent' to backup our SQL DB - I also run a maintenance job (via EM) to backup the DB throughout the day)4) I have never restored our database from the 'file' backup that is created via the maintenance plan - How is this done? Note: I have tried restoring our DB backup from Veritas and it works fine, would just like to know how to do it from the file backup that is created via our maintenance plan?5) Once a DB is restored, how do you replay the latest transaction log files etc?..Notes: We are running SQL Server 2000 Std Ed and our database is only around 250MBYes, I'm a complete noob:)Many thanksras |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-22 : 08:56:56
|
| To restore the database:Firstly, were you backing up the transaction logs of the database?Why DBCC CheckDB didn't report no errors:Was the Server up when the DBCC job was running? i.e. is the server shut down in the middle of the night for example. The job will not be highlighted as failed if SQL Server was not up.You can determine this from the log file. When was the database last started up? |
 |
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-22 : 09:03:44
|
| Hi drewsalem,-The transaction logs are backed up with the database (every night via the Veritas SQL Agent). How can you enable transaction log backups via Enterprise Manager?- The server is always up and was not down last night etc- The database is always online. I have only ever had to restore it once before (about 7 months ago - due to a disaster recovery scenario). This went fineAny help would be greatly appreciatedcheers materas |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-22 : 09:29:51
|
| It maybe that the errors occured after the DBCC check was run. What is the time stamp in the Event log and what time were the checks run?What is the actual error in the log?What syntex did you use to repair the suspect DB?I have no experience with Veritas products, though I'm sure someone who has will post a response soon. **It maybe that you cannot run the Veritas backups along side SQL Server tlog backups and will cause problems.**None-the-less for reference. In Enterprise Manager, make sure the database recovery model is set to FULL (right click on the database, click on Properties, click on the Options tab, select FULL where is says model)Click on your current Backup maintenance plan and select the transaction log backup tab. Select your options (such as how often to backup the transaction log).This will start backing up transaction logs on a regular basis.When you come to restore, right click on the database, select All Tasks -> Restore Database. It may hang a while if you have many transactional logs (which is why it's good to learn how to use T-SQL to do it).You will get a window with the backups listed. The Complete Backups will be listed on the left, the relevent tlog back ups indented to the right after each complete backup. Look at the time of the Complete backups and choose which one you want to use to restore the database. When you've ticked the one you want, all the needed tlogs will be automatically selected for you. But double check them anyway. And then click ok to restore. There is also an option there to restore to a particular point in time (15:09 on Thursday). Create your own dummy database and a table in it and practise backing up. Make changes to the table and the restore. Have the changes gone?But check the Veritas situation first!There are hundreds of friendly guides on the net, google it. |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-22 : 09:39:55
|
| Drew, many thanks for your swift replies - it is much appreciatedMany thanks indeed |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-22 : 10:02:29
|
| What is the normal syntax to repair a database? many thanks againras |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-22 : 14:03:39
|
quote:
Originally posted by ras2a
What is the normal syntax to repair a database? many thanks againras
Can you post details of the errors you had in the errorlog please?If CHECKDB is clean, then you don't need to do anything except work out what the errors were that you saw in the errorlog.Read my blog post on repair here (http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx) - in fact it sounds like you should go and read all my blog posts.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-22 : 14:42:42
|
| Cheers Paul - appreciate that. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-23 : 04:24:32
|
| It will be a while yet before I can afford Sushi, Paul!...Any jobs going at Microsoft?! |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-06-23 : 14:36:44
|
quote:
Originally posted by drewsalem
It will be a while yet before I can afford Sushi, Paul!...Any jobs going at Microsoft?!
http://members.microsoft.com/careers/default.mspxPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-06-23 : 15:27:38
|
| ras,errors in your eventlog don't mean that your database is corrupt.I suggest you look up the event ids of the errors you are seeing. http://eventid.net is a good place to start to get an idea of what is happening. You will obviously want to elimintate any scary errors so you don't wind up with a corruption problem.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-06-23 : 15:31:19
|
quote:
Originally posted by drewsalem
It will be a while yet before I can afford Sushi, Paul!...
well, hopefully you can afford the Sam Adams then..-ec |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-29 : 05:32:15
|
| eyechart, the errors in the event log pertained directly to DBCC errors, this was confirmed when checking via QA. Regardless, this problem has now been solved and I have a maintenance plan set-up for backing up the db and logs to a seperate disk (files over 4 wks old are deleted). Also, regarding eventid.net - Yeah, I've used that site for a while now, very useful information indeed.cheers dude |
|
|
|
|
DBCC not regonising corruption?