SQL Server Performance

Transation Log File is missing/Lost

Discussion in 'SQL Server 2005 General DBA Questions' started by y.koteswarrao, Nov 7, 2008.

  1. y.koteswarrao New Member

    Hi DBA guru's
    How to recover/get back the lost/missing Transaction log file?
    Koteswar Rao.Y
  2. madhuottapalam New Member

    You can restore/recover your database even if you do not have TL file provided the database was cleanly shutdown. You can either use sp_attach_single_file_db or create database for attach command. Read about these commands in BOL
    If you have MDF you may try this steps
    create a database with the same name in another directory as the one you're trying to attach
    re-create all filesgroups and files as necessary
    shutdown the server
    swap in the old mdf file and any ndf files
    bring up the server and let the database attempt to be recovered and then go into suspect mode
    put the database in single_user and emergency modes
    run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
    return database to online, multi_user mode
    Read more about this in SqlSkills.com.
    Thanks
    Madhu
  3. rohit2900 Member

    Hello Koteshwer,
    Can you confirm whether ur asking about the transaction log backup file or the physical database log ( .ldf) file.
  4. dekj New Member

    I found myself in the position of having an MDF file (and an NDF file, a expansion file that placed several tables on another drive), with no LDF log file and no backup. Things happen in life, and this happened to me. As it turns out, SQL Server will not act on any database without a log file. Period. Upon googling this situation, I found that there were ways of recreating the log file in SQL Server 2003 and 2005, but those mechanism were taken out of SQL Server 2008, the version I was running on. I also found the “SQL Server experts” that answered the calls of help for this situation in internet forums were of no help. They mainly seemed to respond by describing the hapless users in this situation as idiots residing in a hopeless situation.
    I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.
    1) Stop the SQL Server Management Studio. At a command prompt, enter
    net stop "SQL Server (MSSQLSERVER)"
    this ended up stopping the SQL Server and the SQL Server Agent.
    2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours
    3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter
    net start "SQL Server (MSSQLSERVER)"
    net start "SQL Server Agent (MSSQLSERVER)"
    This restarted these two services, which had been stopped previously.
    4) In SQL Server Management Studio, right-click the POSProd database, and delete it.
    5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.
    6) Stop the SQL Server Management Studio. At a command prompt, enter
    net stop "SQL Server (MSSQLSERVER)"
    this ended up stopping the SQL Server and the SQL Server Agent.
    7) Delete the new MDF (and NDF) files, but leave the LDF log file.
    8) Rename the old MDF (and NDF) files back to their original names
    9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter
    net start "SQL Server (MSSQLSERVER)"
    net start "SQL Server Agent (MSSQLSERVER)"
    This restarted these two services, which had been stopped previously.
    10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.
    11) Open a new query in SQL Server. Alter the POSProd database to emergency mode
    alter database POSProd set emergency
    12) Set the database to single user mode. Without this, the checkdb command won’t run.
    ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT
    13) Run dbcc checkdb
    dbcc checkdb ('POSProd',repair_allow_data_loss)
    Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.
    14) Alter the database back to multi user mode
    ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT
    15) Set the database online.
    alter database POSProd set online
    16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.
    17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.
    Voila!! The database (in my case) was completely back. All the tables, every row of data.
    Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.
  5. Luis Martin Moderator

    Welcome to the forums!!.
    Thanks for sharing.
    We have one place for that kind of information:
    http://sql-server-performance.com/Community/forums/35.aspx
    Since this one is an old thread (2008) is better to write your expertise in link before.

Share This Page