I had to go through this today, and thought it may help others in the future.
If you have an inconsistent mdf file only, you can’t attach the database using the without logs option. Obviously it is not supported.
So the only way to get around it seems to be creating a new database, shut down SQL Server instance, replace the mdf file, and start up SQL Server instance.
Now the database will go so suspect or other undesired state as you can see from sys.databases view.
Now here are the commands to rebuild, ignore the outstanding transaction, allowing data loss option to bring up your database:
alter database DBName set emergency
alter database DBName set single_user
dbcc checkdb (DBName, REPAIR_ALLOW_DATA_LOSS)
Note: The original log file location must be available on the new host, otherwise you will get an error on dbcc step.
Disclaimer: This is definitely not recommended steps to recovery production database. The proper way would be restore from backup and roll forward to point of failure. It may leave you with an inconsistent database at the end.
Reference:
0 Comments.