If you are unable to Attach the database after deleting the log file and you get the error message:
The physical file ...... may be incorrect
This error happens when a database was not shutdown cleanly we are unable to attach it property with the command:--
--
EXEC sp_attach_single_file_db @dbname = 'MyDB',
@physname = N'C:\Program FilesSQL Server\\Data\MyDB';
--
--
To fix this problem we have to:
-Create a database of equal size to the one you're trying to attach
-Shutdown the server
-Swap in the old mdf file
-Bring up the server and let the database attempt to be recovered and then go into suspect mode
-Put the database into emergency mode with ALTER DATABASE
--
--
EXEC sp_resetstatus 'MyDB';
ALTER DATABASE MyDBSET EMERGENCY
--
--
If you can not do it, in the sysdatabase look for your db and set the stats field to 32768
After the db is in emergency mode you can read from it using enterprise manager SQL 2005
-Bringup the database in emergency mode, you should be able to read from it
If the DB is corrupted or if it is in "Suspect" mode run the command to rebuild:
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS)
--
--
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
--
--
-Create a new database and extract the data from the database that is in emergency mode
The database should be ready to be used, there is a probability you lost some information or the tables lost the default values and identities.
This is a great article with all the steps:
http://www.gerixsoft.com/blog/mssql/recovering-mssql-suspect-mode-emergency-mode-error-1813
