Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create
a new database and copy all the data from the bad db to the new db.
-Jimmy
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
> ****************************************
******************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
Talia
From EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>
|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>************************************************* *********
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.
>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>**********************************************************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

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.
> >.
> >