Issue

A sql database generally has at least two files, a .mdf (data) file and a .ldf (log) file. Databases can have more than 1 of each of these files but for Control there are always two files unless a user manually created some additional ones themselves. At times when dealing with corruption of databases or possibly a mishap with transferring the physical database files you end up losing the .ldf (log) file for the database and are now left with a database you can not attach or utilize in any manner.

The steps below were from various articles obtained from Google searches on SQL corruption and repair.

Resolution

Restore a recent backup if you have one that will not result in considerable data re-entry, else you may attempt either of these resolutions:

Resolution #1

Use sp_attach_single_file_db to attempt to attach the .mdf (data) file that you have present.
USE master;
GO
EXEC sp_detach_db @dbname = 'StoreData';
EXEC sp_attach_single_file_db @dbname = 'StoreData', 
    @physname = N'C:\Program Files (x86)\Cyrious\Control\StoreData.mdf';

Resolution #2

If Resolution #1 doesn't work, then try the following:
  1. Make sure you have a copy of your.MDF
  2. Create a new database called fake (default file locations)
  3. Stop SQL Service
  4. Delete the fake_Data.MDF and copy your.MDF to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
  5. Start SQL Service
  6. Database fake will appear as suspect in EM
  7. Open Query Analyser and in master database run the following :
    -- This will put the database in emergency recovery mode
         sp_configure 'allow updates',1
         GO
         reconfigure WITH override
         GO
         UPDATE sysdatabases SET
            STATUS=-32768 WHERE dbid=DB_ID('fake')
         GO
         sp_configure 'allow updates',0
         GO
         reconfigure WITH override
         GO
  8. Stop SQL Service
  9. Delete the fake_Log.LDF file
  10. Restart SQL Service
  11. In SQL Management Studio (Query Window) run the following (with correct path for log)
    dbcc rebuild_log('fake','h:\fake_log.ldf')
        GO
        dbcc checkdb('fake') -- to check for errors
        GO
  12. Now we need to rename the files, run the following (make sure there are no connections to it) in SQL Management Studio (Query Window) (At this stage you can actually access the database so you could use DTS or bcp to move the data to another database).
    USE master
        GO
        sp_helpdb 'fake'
        GO
        /* Make a note of the names of the files , you will need them
        in the next bit of the script to replace datafilename and
        logfilename - it might be that they have the right names  */
        sp_renamedb 'fake','your'
        GO
        ALTER DATABASE your
            MODIFY FILE(NAME='datafilename', NEWNAME = 'your_data')
        GO
        ALTER DATABASE PowerDVD301
            MODIFY FILE(NAME='logfilename', NEWNAME = 'your_Log')
        GO
        dbcc checkdb('your')
        GO
        sp_dboption 'your','dbo use only','false'
        GO
        USE your
        GO
        sp_updatestats
        GO
  13. You should now have a working database. However the log file will be small so it will be worth increasing its size. Unfortunately your files will be called fake_Data.MDF and fake_Log.LDF but you can get round this by detaching the database properly and then renaming the files and reattaching it.
  14. Run the following in SQL Management Studio (Query Window).
    sp_detach_db your
        --now rename the files then reattach
        sp_attach_db 'your','your.mdf','your.ldf'

Version Information

  • Entered : Brandon Readlinger, 8/27/2014

See Also