Restoring a suspected Database

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.

All Forums

General SQL Server Forums

Data Corruption Issues

Restoring a suspected Database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-28 : 08:12:38

AMIT writes "DEAR SIR,

i have a database which is marked suspected.
i have tried to recover it but i failed to recover because its
data file (.mdf) has one or more bad pages due to which the following procedure which i have followed , has been failed.

i have used the following procedure :

1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up

update sysdatabases set status=32768 where name ='dbname'

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf')

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

but it was failed.

i have no recent backup of it ?

pls . provide any solution for it.

yours amit
A Rising DBA."

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-28 : 14:20:53

Rebuilding the log is about the worst thing you could have done here.

Firstly, you've just destroyed any transactional integrity your database might have had. Rebuilding the log simply throws away all the transaction log, including all info needed to run crash recovery at db startup.

Secondly, rebuilding the log in no way helps a damaged MDF.

Your only option now is to set the DB in emergency mode again and manually extract out as much info as you can. You should also revisit your disaster recovery strategy.

Can you provide details of why the db went suspect (errors you saw/power outage?) and what prompted you to rebuild the transaction log?

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources