locked
Database Recovery RRS feed

  • Question

  • I'm attempting to recover a database for a client.  They've sent me both their .mdf and .ldf files.  With that said, the normal method I use for recovery isn't working.

    Unfortunately I don't know the circumstances which caused their problem to begin with.  What I do know is that I can't attach the data or restore using the management studio using generally excepted practices.

    What I have done.
    1. Created database under my SQL instance to match that of the one I am attempting to recover.
    2. stopped SQL services
    3. Replace .mdf and .ldf with the client database
    4. start SQL services
    *when I start management studio, my database is in suspect mode (which is how I normally get started).

    Generally if I run the follow script I will recover (sometimes in part) what I've lost.

    Declare @DB sysname;

    set @DB = 'DBName';

    -- Put the database in emergency mode

    EXEC('ALTER DATABASE [' + @DB + '] SET EMERGENCY');

    -- Set single user mode

    exec sp_dboption @DB, 'single user', 'TRUE';

    -- Repair database

    DBCC checkdb (@DB, repair_allow_data_loss);

    -- Turn off single user mode

    exec sp_dboption @DB, 'single user', 'FALSE';


    Now, here is what I'm getting when I run that:


    Msg 5123, Level 16, State 1, Line 15
    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.LDF'.
    Msg 5024, Level 16, State 2, Line 15
    No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
    Msg 5028, Level 16, State 2, Line 15
    The system could not activate enough of the database to rebuild the log.
    DBCC results for 'Amicus'.
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'Amicus'.
    Msg 7909, Level 20, State 1, Line 15
    The emergency-mode repair failed.You must restore from backup.


    We'll start there and I'll provide more information if anyone needs any.

    I'll be honest, myself and the SQL developer we have on staff are stumped.


    Thanks.



    Monday, September 8, 2008 8:00 PM

Answers

  • hi,

     

    please note that not all third party snapshot solutions integrate with the vss provider for sql server, in which case you might get torn writes and hence a corrupt database.

     

    what you get when attaching the customers' files indicates that either the log file got corrupted, or the backed up mdf and ldf files are from different point in time (which could easily happen if the backup solution did not synchronise with vss).

     

    you cannot attach a database in emergency mode, so here is what you could try to do (it worked for me).

     

    NOTE : The following steps make non-reversible changes to database, so always make backups before attempting either !

     

    1) try to attach the database as a single file db, but do not copy the log file, only mdf (e.g., make sure sql server cannot find the .ldf file for the database, because otherwise it will try to use it and you will get the same error):

     

    exec sp_attach_single_file_db '<db name>', '<path to mdf>'

     

    that way, sql server will realise that this database does not have log and will automatically rebuild the log. beware that this might not work if the database has any outstanding transactions that require the log in order to be rolled back, in which case you will get an error saying something like that and attach will fail:

     

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.ldf" may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down

     

    if (1) fails, your other resort is :

     

    2) as you tried initially, to create a database with the same name, shutdown the server and then overwrite the .mdf file for the database you just created with what your customer sent you. make sure you don't copy the log and also delete the existing .ldf file so sql server cannot find it. that way, when you start the server, the database will go suspect since the log will be missing. then you can put the database in emergency mode and run dbcc repair_allow_data_loss to rebuild the log.

     

    also, see this nice post by paul randal or emergency database recovery : http://www.sqlskills.com/blogs/paul/2007/10/02/CHECKDBFromEveryAngleEMERGENCYModeRepairTheVeryVeryLastResort.aspx

     

    hope this helps,

    -kal.

    Wednesday, September 10, 2008 5:08 PM

All replies

  • hi,

     

    did you try using sp_attach_db instead of creating a new database and replacing its files ?  with replacing the database files, sql server will try to look for the same paths that existed on your customer's machine, which might not match what you have.

     

    see the instructions in this kb article for more information : http://support.microsoft.com/kb/224071.

     

    -kal.

    Monday, September 8, 2008 9:09 PM
  • Also ensure that Sql Service account has modify permissions to D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.LDF and mdf files. Can you try this way, if your database has only a single ldf and single mdf file try to use sp_attach_single_file_db procedure and see how it goes. For multiple database files it might not work.

    - Deepak
    Tuesday, September 9, 2008 12:51 AM
  • I attempted what you suggested but I just get more of the same (see below).

    I run:
    EXEC sp_attach_single_file_db @dbname='Amicus', @physname='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Pillsbury\Amicus.MDF'

    I get:
    File activation failure. The physical file name "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.LDF" may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'Amicus'. CREATE DATABASE is aborted.

    I called the client, sure enough the database did not shut down cleanly.  As far as their technician was concerned SQL Server arbitrarily just dropped and deleted the database on it's own.

    The files he was kind enough to send me were from a server snapshot from the sounds of it.

    Moving forward.
    The above error message prompted me to search for alternatives to when the database isn't shut down cleanly.  I found this forum thread on the subject: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=630182&SiteID=1

    In the interest of assuring myself I haven't forgotten to do anything, I follow the above linked steps (similar to what I had already done) and got the same result.

    -----
    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Amicus_log.LDF' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
    Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.LDF'.
    Msg 5024, Level 16, State 2, Line 1
    No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
    Msg 5028, Level 16, State 2, Line 1
    The system could not activate enough of the database to rebuild the log.
    DBCC results for 'Amicus'.
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'Amicus'.
    Msg 7909, Level 20, State 1, Line 1
    The emergency-mode repair failed.You must restore from backup.
    -----

    Now the message about the Log file not matching makes sense, because it is indeed bases off the new database I created as per the instructions I linked above.  I just don't know how to remedy that issue.

    Thanks in advance.
    Tuesday, September 9, 2008 3:03 PM
  • when you say "The files he was kind enough to send me were from a server snapshot from the sounds of it.", do you mean a database snapshot ?  that's very important to confirm, because if that's the case, then you have no way of attaching it.

     

    if this is not a snapshot, then what happens when you try to attach using the regular sp_attach_db command (you cannot use attach single file db if the database was not shutdown properly) ?  please if possible, send detailed info on the file locations that are in your environment and the exact commands you use + the output.

     

    -kal.

    Tuesday, September 9, 2008 4:51 PM
  • I finally got a hold of the technician at this site.

    The copies of the .mdf and .ldf that they've sent me are from a snapshot made through a third party of the actual server machine, not just the SQL server. 

    As for your suggestion to attempt using sp_attach_db I get the following output.

    1. In the case where I use the .ldf file they've sent me:
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'Amicus'. CREATE DATABASE is aborted.

    (0 row(s) affected)
    Msg 9003, Level 20, State 9, Line 1
    The log scan number (167548:19:1) passed to log scan in database 'Amicus' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

    2. In the case where I use the .ldf file that was generated from my steps above:
    Msg 5173, Level 16, State 2, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

    (0 row(s) affected)

    I'm running the below code snippet in both cases:
    USE master;
    GO

    IF DB_ID (N'Amicus') IS NOT NULL
    DROP DATABASE Amicus;
    GO

    EXEC sp_attach_db @dbname = N'Amicus',
        @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Pillsbury\Amicus.mdf',
        @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Pillsbury\Amicus_log.ldf';

    -- Verify the database files and sizes
    SELECT name, size, size*1.0/128 AS [Size in MBs]
    FROM sys.master_files
    WHERE name = N'Amicus';
    GO

    Thanks again for the help.
    Tuesday, September 9, 2008 8:12 PM
  • hi,

     

    please note that not all third party snapshot solutions integrate with the vss provider for sql server, in which case you might get torn writes and hence a corrupt database.

     

    what you get when attaching the customers' files indicates that either the log file got corrupted, or the backed up mdf and ldf files are from different point in time (which could easily happen if the backup solution did not synchronise with vss).

     

    you cannot attach a database in emergency mode, so here is what you could try to do (it worked for me).

     

    NOTE : The following steps make non-reversible changes to database, so always make backups before attempting either !

     

    1) try to attach the database as a single file db, but do not copy the log file, only mdf (e.g., make sure sql server cannot find the .ldf file for the database, because otherwise it will try to use it and you will get the same error):

     

    exec sp_attach_single_file_db '<db name>', '<path to mdf>'

     

    that way, sql server will realise that this database does not have log and will automatically rebuild the log. beware that this might not work if the database has any outstanding transactions that require the log in order to be rolled back, in which case you will get an error saying something like that and attach will fail:

     

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Amicus_log.ldf" may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down

     

    if (1) fails, your other resort is :

     

    2) as you tried initially, to create a database with the same name, shutdown the server and then overwrite the .mdf file for the database you just created with what your customer sent you. make sure you don't copy the log and also delete the existing .ldf file so sql server cannot find it. that way, when you start the server, the database will go suspect since the log will be missing. then you can put the database in emergency mode and run dbcc repair_allow_data_loss to rebuild the log.

     

    also, see this nice post by paul randal or emergency database recovery : http://www.sqlskills.com/blogs/paul/2007/10/02/CHECKDBFromEveryAngleEMERGENCYModeRepairTheVeryVeryLastResort.aspx

     

    hope this helps,

    -kal.

    Wednesday, September 10, 2008 5:08 PM
  • Thanks Kal for the nice blog! It works for me! Thank you!

    Friday, November 14, 2008 9:32 PM
  • Hi Jessie J,

    Can you provide me what's in Karls blog.

    I cannot view it.  

    Thanks in advance.

    http://www.sqlskills.com/blogs/paul/2007/10/02/CHECKDBFromEveryAngleEMERGENCYModeRepairTheVeryVeryLastResort.aspx

    Sunday, April 22, 2012 9:43 AM