Help with Recovery

My W2K Server crashed during a long update operation and now the
database is marked Suspect. I have no backup.
Is there any way I can revive the database from the .mdf and .ldf files
?
Please help.try
==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============
<Iyengar.Raghu@.gmail.com> wrote in message
news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.
>|||try sp_resetstatus
Iyengar.Raghu@.gmail.com wrote:
> My W2K Server crashed during a long update operation and now the
> database is marked Suspect. I have no backup.
> Is there any way I can revive the database from the .mdf and .ldf files
> ?
> Please help.|||Hi,
DBCC REBUILD_LOG is an undocumented method and this will create a new log
file. This might cause some inconsistancy in data.
Since you do not have the backup you can an go with this method as Uri
pointed out. But please do a perfect backup strategy
(Full db backup and Transaction log backup) to ensure that you could
recover the database at any point.
THanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uKBI24X3GHA.3508@.TK2MSFTNGP03.phx.gbl...
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system
> tables:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on
> the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...
>|||Thank you Uri.
I followed your process step by step the DBCC Check returned a few
warnings but I did not notice anything fatal.
I have completed all the 7 steps but the database is still in the
suspect status.
Is there anything else I should be doing ?
Raghu
Uri Dimant wrote:[vbcol=seagreen]
> try
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
> 2. Change the database context to Master and allow updates to system table
s:
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
> 4. Stop and restart SQL server.
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on th
e
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
> 7. Turn off the updates to system tables by using:
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
> <Iyengar.Raghu@.gmail.com> wrote in message
> news:1158843367.407948.245680@.b28g2000cwb.googlegroups.com...|||Thank you all. I worked.
bazoorg@.gmail.com wrote:[vbcol=seagreen]
> try sp_resetstatus
>
> Iyengar.Raghu@.gmail.com wrote: