Search code examples
sql-serverrecoverydata-recovery

Open SQL Server .mdf and .ldf files from recovery

Recently I had a problem in a remote server which contains an small SQL Server running on.

I used PhotoRec 7.0 for recovering all files including the files mdf and ldf but now I don't have any relationship between names and files. See example attached below:

example files

I couldn't attach mdf files on SQL Server Studio as there are no ldf files with the same name in the same folder.

I've used a third party tool for opening mdf files, and I'm able to see the contents on that files, but there were no luck for inspecting ldf.

Any ideas for relation mdf files with their respective ldf? Or a way for omitting the ldf file in order to import mdf file in SQL Server Studio?

Solution

  • Below are the sequence of steps and you must be aware that,this database may not be transactionally consistent.

    Below are steps on how to get your db back online

    1.Create a database with same name (let's say newdb )
    2.Shut down the server
    3.Replace newdb mdf ,ldf files with your oldones(olddb) and try to start sql server
    4.Your newdb won't start and may* go into suspect state
    5.If this is the case,try setting the database to emergency and set to singleuser like below

    alter database dbname set SET EMERGENCY;
alter database dbname  SET SINGLE_USER

    6.now try running DBCC checkDB

    DBCC CHECKDB (N'dbname', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

    99% of issues will be fixed by DBCC and if so you are in luck..

    Now trying setting the DB online and multi user

    *There might be a chance,that your database is cleanly shutdown(no active transactions),if this is the case, you can try running below command ,prior to above steps.SQL will create a new log file for you

    Create database dbname 
    On 
    (   
    Filename= 'path where you copied files'   
    )
    For attach;

    References:
    http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/
    http://www.sqlskills.com/blogs/paul/corruption-last-resorts-that-people-try-first/