Recovering DBs with a "Suspect" state

Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.Depends on why it is suspect. If you are lucky, someone simply moved the database files, and you just beed to move them back. if you have average luck, then you are looking at a database restore. What does the errorlog say?|||before you decide what to do, you ought to determine the cause.

but you can either...

a. you might be able to put your database in emergency mode and extract your data using bcp or dts.

or

b. restore from your last good backup.

I practice full recovery and can recover to any point in time, so I usually go with b and I have never done a, but I hear it is possible in some situations.

Recover data from corrupt DB

Hi all:
I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
VHD files, I never backed up the DBs themselves. Now for some reason all of
the images have an error in the MSCRM Metabase database, and I cannot
recover it. I tried this:
1. alter database Adventure_Works_Cycle_METABASE set emergency
2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
Which got the DB back online, but it dumped a bunch of necessary data and
broke MSCRM.
Can anyone make any suggestions as to how I can save the data? The metabase
tracks customizations to MSCRM, so if I lose it I'll lose all of my work.
Thanks in advance for any and all help.
John.
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/
John
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"John" <jstraumann@.nospam.hotmail.com> wrote in message
news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
> Hi all:
> I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
> VHD files, I never backed up the DBs themselves. Now for some reason all
> of the images have an error in the MSCRM Metabase database, and I cannot
> recover it. I tried this:
> 1. alter database Adventure_Works_Cycle_METABASE set emergency
> 2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
> 3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
> 4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
> Which got the DB back online, but it dumped a bunch of necessary data and
> broke MSCRM.
> Can anyone make any suggestions as to how I can save the data? The
> metabase tracks customizations to MSCRM, so if I lose it I'll lose all of
> my work.
> Thanks in advance for any and all help.
> John.
> --
> This message sent from Windows Vista Ultimate
> http://mscrmguy.blogspot.com/
>
|||Thanks but those steps only work if the DB is backed up.
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23039dpcGHHA.1188@.TK2MSFTNGP06.phx.gbl...
> John
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
> "John" <jstraumann@.nospam.hotmail.com> wrote in message
> news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
>

Recover data from corrupt DB

Hi all:
I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
VHD files, I never backed up the DBs themselves. Now for some reason all of
the images have an error in the MSCRM Metabase database, and I cannot
recover it. I tried this:
1. alter database Adventure_Works_Cycle_METABASE set emergency
2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
Which got the DB back online, but it dumped a bunch of necessary data and
broke MSCRM.
Can anyone make any suggestions as to how I can save the data? The metabase
tracks customizations to MSCRM, so if I lose it I'll lose all of my work.
Thanks in advance for any and all help.
John.
--
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/John
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"John" <jstraumann@.nospam.hotmail.com> wrote in message
news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
> Hi all:
> I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
> VHD files, I never backed up the DBs themselves. Now for some reason all
> of the images have an error in the MSCRM Metabase database, and I cannot
> recover it. I tried this:
> 1. alter database Adventure_Works_Cycle_METABASE set emergency
> 2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
> 3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
> 4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
> Which got the DB back online, but it dumped a bunch of necessary data and
> broke MSCRM.
> Can anyone make any suggestions as to how I can save the data? The
> metabase tracks customizations to MSCRM, so if I lose it I'll lose all of
> my work.
> Thanks in advance for any and all help.
> John.
> --
> This message sent from Windows Vista Ultimate
> http://mscrmguy.blogspot.com/
>|||Thanks but those steps only work if the DB is backed up.
--
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23039dpcGHHA.1188@.TK2MSFTNGP06.phx.gbl...
> John
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
> "John" <jstraumann@.nospam.hotmail.com> wrote in message
> news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
>> Hi all:
>> I have MSCRM running in a VPC setup, so since I had 4 backup copies of
>> the VHD files, I never backed up the DBs themselves. Now for some reason
>> all of the images have an error in the MSCRM Metabase database, and I
>> cannot recover it. I tried this:
>> 1. alter database Adventure_Works_Cycle_METABASE set emergency
>> 2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
>> 3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
>> 4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
>> Which got the DB back online, but it dumped a bunch of necessary data and
>> broke MSCRM.
>> Can anyone make any suggestions as to how I can save the data? The
>> metabase tracks customizations to MSCRM, so if I lose it I'll lose all of
>> my work.
>> Thanks in advance for any and all help.
>> John.
>> --
>> This message sent from Windows Vista Ultimate
>> http://mscrmguy.blogspot.com/
>

Recover data from corrupt DB

Hi all:
I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
VHD files, I never backed up the DBs themselves. Now for some reason all of
the images have an error in the MSCRM Metabase database, and I cannot
recover it. I tried this:
1. alter database Adventure_Works_Cycle_METABASE set emergency
2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
Which got the DB back online, but it dumped a bunch of necessary data and
broke MSCRM.
Can anyone make any suggestions as to how I can save the data? The metabase
tracks customizations to MSCRM, so if I lose it I'll lose all of my work.
Thanks in advance for any and all help.
John.
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/John
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
"John" <jstraumann@.nospam.hotmail.com> wrote in message
news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
> Hi all:
> I have MSCRM running in a VPC setup, so since I had 4 backup copies of the
> VHD files, I never backed up the DBs themselves. Now for some reason all
> of the images have an error in the MSCRM Metabase database, and I cannot
> recover it. I tried this:
> 1. alter database Adventure_Works_Cycle_METABASE set emergency
> 2. sp_dboption Adventure_Works_Cycle_METABASE, single, true
> 3. dbcc checkdb (Adventure_Works_Cycle_METABASE, repair_allow_data_loss)
> 4. sp_dboption Adventure_Works_Cycle_METABASE, single, false
> Which got the DB back online, but it dumped a bunch of necessary data and
> broke MSCRM.
> Can anyone make any suggestions as to how I can save the data? The
> metabase tracks customizations to MSCRM, so if I lose it I'll lose all of
> my work.
> Thanks in advance for any and all help.
> John.
> --
> This message sent from Windows Vista Ultimate
> http://mscrmguy.blogspot.com/
>|||Thanks but those steps only work if the DB is backed up.
This message sent from Windows Vista Ultimate
http://mscrmguy.blogspot.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23039dpcGHHA.1188@.TK2MSFTNGP06.phx.gbl...
> John
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
>
> "John" <jstraumann@.nospam.hotmail.com> wrote in message
> news:929ACB25-076E-4714-AC77-144F44FD319B@.microsoft.com...
>

Records not inserting...

I am totally new to SQL server. I am moving all of our Access DB's over to
an SQL server DB - and am only using Access as the front end interface and
development tool.
The tables came across no problem and after I relinked all of them to the
SQL server DB tables using the ODBC driver, the app even ran without anyt
errors!! I was impressed.
However, something strange is happening that I am sure a seasoned SQL server
person will know...
When a user changes certain data, in my application code, I write changelog
records of each change.
To do this I "insert" a record into a table. THis is not happening and no
errors are being generated - at least none that I can see...
I stepped through my code and the insert command runs fine and does not go
through the "on error" routine. However, when I check the table after I have
inserted a record, none has been inserted.
I have made sure that the logged on user (me) has insert rights etc...
What should I be checking?
PS...Now that I am using SQL server, I suspect it would be easy to write a
trigger that automatically inserts records in a table instead of placing
these actions in my application code. However, in a trigger, would I be able
to reference what the old value was before the change so I have a record of
what the old value/new values are?
Thanks,
Brad
"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O1dX3IEuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> I am totally new to SQL server. I am moving all of our Access DB's over to
> an SQL server DB - and am only using Access as the front end interface and
> development tool.
> The tables came across no problem and after I relinked all of them to the
> SQL server DB tables using the ODBC driver, the app even ran without anyt
> errors!! I was impressed.
> However, something strange is happening that I am sure a seasoned SQL
server
> person will know...
> When a user changes certain data, in my application code, I write
changelog
> records of each change.
> To do this I "insert" a record into a table. THis is not happening and no
> errors are being generated - at least none that I can see...
> I stepped through my code and the insert command runs fine and does not go
> through the "on error" routine. However, when I check the table after I
have
> inserted a record, none has been inserted.
> I have made sure that the logged on user (me) has insert rights etc...
> What should I be checking?
You need to turn on ODBC tracing. Put a break in your code just before the
INSERT. Turn on ODBC tracing when i braks. Run the INSERT statement. Turn
off Tracing. View the trace log and see what was actually sent.

> PS...Now that I am using SQL server, I suspect it would be easy to write a
> trigger that automatically inserts records in a table instead of placing
> these actions in my application code. However, in a trigger, would I be
able
> to reference what the old value was before the change so I have a record
of
> what the old value/new values are?
Yes, you can! From SQL Server Books Online (partial paste):
Two special tables are used in trigger statements: the deleted table and the
inserted table. Microsoft SQL Server 2000 automatically creates and
manages these tables. You can use these temporary, memory-resident tables to
test the effects of certain data modifications and to set conditions for
trigger actions; however, you cannot alter the data in the tables directly.
The inserted and deleted tables are used primarily in triggers to:
a.. Extend referential integrity between tables.
b.. Insert or update data in base tables underlying a view.
c.. Check for errors and take action based on the error.
d.. Find the difference between the state of a table before and after a
data modification and take action(s) based on that difference.
The deleted table stores copies of the affected rows during DELETE and
UPDATE statements. During the execution of a DELETE or UPDATE statement,
rows are deleted from the trigger table and transferred to the deleted
table. The deleted table and the trigger table ordinarily have no rows in
common.
The inserted table stores copies of the affected rows during INSERT and
UPDATE statements. During an insert or update transaction, new rows are
added simultaneously to both the inserted table and the trigger table. The
rows in the inserted table are copies of the new rows in the trigger table.
An update transaction is similar to a delete operation followed by an insert
operation; the old rows are copied to the deleted table first, and then the
new rows are copied to the trigger table and to the inserted table.

>
> Thanks,
> Brad
>
>