.MDF Corrupted

Question: I do not have full backup of my database. My .mdf and .ldf are corrupted. Is there any way I can restore database now?


Answer: Sorry. I do not think there is any way you can do it. Try attaching this files to database using db_attach but if that does not work, it will be very difficult make it work.
Make sure you have taken backup of your physical file and also this exercise you do at your own risk.
ALTER DATABASE test
SET emergency
GO
ALTER DATABASE test
SET single_user
GO
DBCC checkdb (test, repair_allow_data_loss)
ALTER DATABASE test
SET multi_user
GO
How to recover a corrupt master MSSQL database.


I am by no means a SQL expert but due to the life of a network engineer I get to see a little of everything. If anyone has any suggestions or comments on the steps I used feel free to make them. Recently we had an issue with a client that there master database was corrupt and MSSQL would not start. We had an old backup of the master database and the .mdf and .ldf of the client databases only. Below I have listed out the steps we took to recover the master database and the client databases.




- We started sqlservr.exe as an application using the trace flag -T3608 and it gave the error:


Error: 9003, Severity: 20, State: 1.


Cannot recover the master database. Exiting. 


- We then proceeded with reinstallation of sql server for a named intance:NEW. and also applied sp3.


- Started sqlservr.exe -c -m -snew


- Restore database master from disk = 'D:\SQLDATAold\mssql\BACKUP\master_db_200503130200.BAK' with replace


- Started sqlservr.exe -T3608 


- Checked the consistency of the Master database using: dbcc checkdb ('MASTER') - zero consistencies


- Next detached the Model database :sp_detach_db 'model'


- Renamed the new instance model db files


- Attached the Model database using: sp_attach_db 'model','D:\sqldata\MSSQL$NEW\Data\model.mdf','D:\sqldata\MSSQL$NEW\Data\modellog.ldf'


- Then took care of the Temp db by : Alter database tempdb modify file (name= 'tempdev', filename = 'D:\sqldata\MSSQL$NEW\Data\tempdb.mdf')


Alter database tempdb modify file (name= 'templog', filename = 'D:\sqldata\MSSQL$NEW\Data\templog.ldf')


- Detached the MSDB database: sp_detach_db 'msdb' and attached it back pointing to the right location: 


sp_attach_db 'msdb','D:\sqldata\MSSQL$NEW\Data\msdbdata.mdf', 'D:\sqldata\MSSQL$NEW\Data\msdblog.ldf'


- Then changed the server name by executing the following commands


- select @@servername


- sp_dropserver 'BTLAPP'


- sp_addserver 'BTLAPP\NEW', 'LOCAL'


- Attached the DOCUWARE database and checked its consistencies: dbcc checkdb ('DOCUWARE') - zero consistencies