DATABASE marked "SUSPECT" - How to restore?

Recently after I rebooted the MS SQL server, the "cdr"
database is marked with "Suspect" in the Enterprise
Manager,and when I try to access the database, the
Enterprise Manager prompted that the database is marked
as "Suspect" and inaccessible. When looked into the
MSSQL\data folder, the .MDB file and LDF is still there.
Strongly believe this is because there was a torn page
happened(because I restarted the server when the pc hung).
The cdr database contain our latest 3 months call records
and it is up to 22G of data. The bad new is, no backup had
been done to restore the data. I've tried to use
sp_attach_db, recreate database and all other method to
restore the data, still failed to restore the data. Since
the MDF file is preserved, hopefully there is a method to
restore the database.hey have a look at this website,
http://www.spaceprogram.com/knowledge/sqlserver_recover_fro
m_deleted_log.html
>--Original Message--
>Recently after I rebooted the MS SQL server, the "cdr"
>database is marked with "Suspect" in the Enterprise
>Manager,and when I try to access the database, the
>Enterprise Manager prompted that the database is marked
>as "Suspect" and inaccessible. When looked into the
>MSSQL\data folder, the .MDB file and LDF is still there.
>Strongly believe this is because there was a torn page
>happened(because I restarted the server when the pc
hung).
>The cdr database contain our latest 3 months call records
>and it is up to 22G of data. The bad new is, no backup
had
>been done to restore the data. I've tried to use
>sp_attach_db, recreate database and all other method to
>restore the data, still failed to restore the data. Since
>the MDF file is preserved, hopefully there is a method to
>restore the database.
>.
>|||Hi,
I got these from another newsgroup several months ago and
they have worked on three suspect databases since. I
generally only need to run the DBCC rebuild_log and then
run a DBCC CheckDB to make sure the database is not
corrupt. Hope they help.
1) Make sure you have a copy of your.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy your.MDF to where
fake_Data.MDF used
to
be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure there
are
no connections to it) in Query Analyzer (At this stage
you can actually
access the database so you could use DTS or bcp to move
the data to
another
database).
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will
need them
in the next bit of the script to replace datafilename
and
logfilename - it might be that they have the right
names */
sp_renamedb 'fake','your'
go
alter database your
MODIFY FILE(NAME='datafilename', NEWNAME
= 'your_data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'your_Log')
go
dbcc checkdb('your')
go
sp_dboption 'your','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file will be
small so it will be worth increasing its size.
Unfortunately your files
will
be called fake_Data.MDF and fake_Log.LDF but you can get
round this by
detaching thedatabase properly and then renaming the
files and reattaching
it.
14) Run the following in QA
sp_detach_db your
--now rename the files then reattach
sp_attach_db 'your','your.mdf','your.ldf'
>--Original Message--
>Recently after I rebooted the MS SQL server, the "cdr"
>database is marked with "Suspect" in the Enterprise
>Manager,and when I try to access the database, the
>Enterprise Manager prompted that the database is marked
>as "Suspect" and inaccessible. When looked into the
>MSSQL\data folder, the .MDB file and LDF is still there.
>Strongly believe this is because there was a torn page
>happened(because I restarted the server when the pc
hung).
>The cdr database contain our latest 3 months call records
>and it is up to 22G of data. The bad new is, no backup
had
>been done to restore the data. I've tried to use
>sp_attach_db, recreate database and all other method to
>restore the data, still failed to restore the data. Since
>the MDF file is preserved, hopefully there is a method to
>restore the database.
>.
>|||Be warned - rebuilding your log is extremely dangerous and UNSUPPORTED (i.e.
you're on your own when it goes wrong - which it can very easily do).
This command commonly results in large amounts of lost data and, much more
seriously, loss of all transactional consistency in your database (i.e. did
that update transaction to a customer's bank account happen or not? there is
no way to tell).
If your business can cope with that, fine.
Microsoft strongly recommends that you DO NOT use this command or the
procedures documented below.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:0e8601c38cc6$5092d840$a301280a@.phx.gbl...
> Hi,
> I got these from another newsgroup several months ago and
> they have worked on three suspect databases since. I
> generally only need to run the DBCC rebuild_log and then
> run a DBCC CheckDB to make sure the database is not
> corrupt. Hope they help.
> 1) Make sure you have a copy of your.MDF
> 2) Create a new database called fake (default file
> locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy your.MDF to where
> fake_Data.MDF used
> to
> be and rename the file to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the
> following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following
> (make sure there
> are
> no connections to it) in Query Analyzer (At this stage
> you can actually
> access the database so you could use DTS or bcp to move
> the data to
> another
> database).
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will
> need them
> in the next bit of the script to replace datafilename
> and
> logfilename - it might be that they have the right
> names */
> sp_renamedb 'fake','your'
> go
> alter database your
> MODIFY FILE(NAME='datafilename', NEWNAME
> = 'your_data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME
> = 'your_Log')
> go
> dbcc checkdb('your')
> go
> sp_dboption 'your','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the
> log file will be
> small so it will be worth increasing its size.
> Unfortunately your files
> will
> be called fake_Data.MDF and fake_Log.LDF but you can get
> round this by
> detaching thedatabase properly and then renaming the
> files and reattaching
> it.
> 14) Run the following in QA
> sp_detach_db your
> --now rename the files then reattach
> sp_attach_db 'your','your.mdf','your.ldf'
>
> >--Original Message--
> >Recently after I rebooted the MS SQL server, the "cdr"
> >database is marked with "Suspect" in the Enterprise
> >Manager,and when I try to access the database, the
> >Enterprise Manager prompted that the database is marked
> >as "Suspect" and inaccessible. When looked into the
> >MSSQL\data folder, the .MDB file and LDF is still there.
> >Strongly believe this is because there was a torn page
> >happened(because I restarted the server when the pc
> hung).
> >The cdr database contain our latest 3 months call records
> >and it is up to 22G of data. The bad new is, no backup
> had
> >been done to restore the data. I've tried to use
> >sp_attach_db, recreate database and all other method to
> >restore the data, still failed to restore the data. Since
> >the MDF file is preserved, hopefully there is a method to
> >restore the database.
> >.
> >