Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kowani1
Starting Member
42 Posts |
Posted - 2006-12-17 : 19:02:12
|
Hi All,My SQL Server 2000 had to be rebooted this morning and I founf out that one of my bigger DBs(50G dd file) came up with a 'suspect' status.Is there a way I can continue to use the existing data file without dropping the DB, then creating new datafiles and recovering from recent backups, coz the current datafile may be corrupted.thanks.jungle dba |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-12-17 : 19:15:28
|
any errors in your error log? what about your system or application event logs?-ec |
|
|
kowani1
Starting Member
42 Posts |
Posted - 2006-12-17 : 19:38:28
|
Event Type: ErrorEvent Source: MSSQLSERVEREvent Category: (2)Event ID: 17052Date: 12/18/2006Time: 7:17:56 AMUser: CORP\admin_iwComputer: BSSWH01Description:Error: 823, Severity: 24, State: 2I/O error (torn page) detected during read at offset 0x0000064aa4a000 in file 'D:\sqldata\Repository\Repository_Data.MDF'. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-12-17 : 22:06:54
|
seehttp://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.htmlIt shows how to access a suspect database towards the end.Your torn page usually means you will beed to restore from backup but by folowing the above you should be able to access most of the data - but not update it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
kowani1
Starting Member
42 Posts |
Posted - 2006-12-17 : 22:36:14
|
thanks for the URL.. It does relate well to my current situation with the 'torn pages' error on my current production MDF.I'm certainly gonna have a icy cold beer if this works.Jungle DBA. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-12-17 : 22:40:01
|
You won't be able to access it via enterprise manager but you can via query analyser.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-18 : 04:24:13
|
If you have a FULL backup from before the problem, and ALL Transaction Backups SINCE there is a good chance you can restore those an get a clean backup.Steps would be:1) Set database to SINGLE USER (i.e. chuck everyone else out)2) Make a FINAL TLog backups3) Restore FULL backup to a NEW TEMPORARY database (preferably on different server) in STANDBY4a) Perform DBCC CHECKDB4b) If errors go back to an earlier FULL backup5) Restore all TLog backups in order6) Perform another DBCC CHECKDB to check the database healthIf that's all OK you should be able to Backup the "temporary" database and restore it to the correct location (make another DBCC CHECKDB after that)TORN PAGE is normally caused by hardware failure - check your Event Log.You may want to have a read through the Data Corruption Issues forum for similar scenarios, and actions that were recommended / triedhttp://www.sqlteam.com/forums/forum.asp?FORUM_ID=22Kristen |
|
|
|
|
|