Re-attaching database and the case of the disappearing transaction log file

  • This probably has happened to you. You detach a database with an mdf, ndf and an ldf file, and then when you want to attach it back, it only has mdf and ndf and no ldf.

    I always thought, this is because the ldf was empty and it got truncated in the process...

    Until today. I have two sql servers, and the same database was restored on both of them. then on one of them I detached and the ldf was gone when I wanted to re-attach. On the second server, when I detach and re-attach, the ldf does not go away.

    I want these to be equal, so can't have one with and one without. I was going to add them to an availability group with join only option. anywas, my question is, why the discrepancy?

  • are the paths the same on both servers? I've never seen a dethatch lose a log file

    I'd wager that the .ldf file is somewhere

    MVDBA

  • on most of my servers, the log files are on a completely different drive; it is more common on bigger installations to seperate the data to multiple drives.

    so the mdf is on the D:(for data) drive

    and the logs are on the L:(for logs) drive.

     

    this snippet grabs the default paths for data and logs and more fromt eh registry,, and it might help you find them easier

     

      IF OBJECT_ID('tempdb.[dbo].[#Result]') IS NOT NULL 
    DROP TABLE [dbo].[#Result]
    CREATE TABLE #Result (ServerName varchar(128) DEFAULT @@SERVERNAME,ValueRead varchar(256),DataRead varchar(256),CleanValue varchar(256),DefaultValue varchar(256))
    DECLARE @ValueRead NVARCHAR(512),
    @SQLPathDirectory NVARCHAR(512),
    @SQLDataRootDirectory NVARCHAR(512),
    @DefaultBackupDirectory NVARCHAR(512),
    @DefaultDataDirectory NVARCHAR(512),
    @DefaultLogDirectory NVARCHAR(512),
    @cmd varchar(max);

    --SQLPath######################################################################################
    SET @ValueRead = N'SQLPath';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\Setup',
    @ValueRead OUTPUT,
    @SQLPathDirectory OUTPUT

    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,@SQLPathDirectory,CASE WHEN @SQLPathDirectory LIKE '%\' THEN @SQLPathDirectory ELSE @SQLPathDirectory + '\' END,CASE WHEN @SQLPathDirectory LIKE '%\' THEN @SQLPathDirectory ELSE @SQLPathDirectory + '\' END

    --SQLDataRoot######################################################################################
    SET @ValueRead = N'SQLDataRoot';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLSERVER\Setup',
    @ValueRead OUTPUT,
    @SQLDataRootDirectory OUTPUT

    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,@SQLDataRootDirectory,CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory ELSE @SQLDataRootDirectory + '\' END,CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory ELSE @SQLDataRootDirectory + '\' END

    --BackupDirectory##################################################################################
    SET @ValueRead = N'BackupDirectory';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultBackupDirectory OUTPUT

    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultBackupDirectory,
    CASE WHEN @DefaultBackupDirectory LIKE '%\' THEN @DefaultBackupDirectory ELSE @DefaultBackupDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultBackupDirectory LIKE '%\' THEN @DefaultBackupDirectory ELSE @DefaultBackupDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Backup\' ELSE @SQLDataRootDirectory + '\Backup\' END)

    --DefaultData######################################################################################
    SET @ValueRead = N'DefaultData';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultDataDirectory OUTPUT

    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultDataDirectory,
    CASE WHEN @DefaultDataDirectory LIKE '%\' THEN @DefaultDataDirectory ELSE @DefaultDataDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultDataDirectory LIKE '%\' THEN @DefaultDataDirectory ELSE @DefaultDataDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Data\' ELSE @SQLDataRootDirectory + '\Data\' END)

    --DefaultLog#######################################################################################
    SET @ValueRead = N'DefaultLog';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultLogDirectory OUTPUT

    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultLogDirectory,
    CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Log\' ELSE @SQLDataRootDirectory + '\Log\' END)
    --Custom AuditBackupRestore##################################################################################
    SET @ValueRead = N'AuditBackupRestore';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultLogDirectory OUTPUT
    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultLogDirectory,
    CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Log\' ELSE @SQLDataRootDirectory + '\Log\' END)
    --Custom DBAEventErrors##################################################################################
    SET @ValueRead = N'DBAEventErrors';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultLogDirectory OUTPUT
    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultLogDirectory,
    CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Log\' ELSE @SQLDataRootDirectory + '\Log\' END)
    --Custom Traces##################################################################################
    SET @ValueRead = N'Traces';
    EXECUTE [master].dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
    @ValueRead OUTPUT,
    @DefaultLogDirectory OUTPUT
    INSERT INTO #Result(ValueRead,DataRead,CleanValue,DefaultValue)
    SELECT @ValueRead,
    @DefaultLogDirectory,
    CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    ISNULL(CASE WHEN @DefaultLogDirectory LIKE '%\' THEN @DefaultLogDirectory ELSE @DefaultLogDirectory + '\' END,
    CASE WHEN @SQLDataRootDirectory LIKE '%\' THEN @SQLDataRootDirectory + 'Log\' ELSE @SQLDataRootDirectory + '\Log\' END)


    SELECT ServerName,
    ValueRead,
    --DataRead,
    CleanValue,
    --DefaultValue ,
    UNCAdminShare = CASE
    WHEN CleanValue LIKE '%:%'
    THEN '\\' + CONVERT(VARCHAR(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) + '\' + REPLACE(CleanValue,':','$')
    ELSE CleanValue
    END
    FROM #Result

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • what happened to good old fashioned command prompt tools

    dir /s *.ldf

    none of this fancy SQL malarkey 🙂

    MVDBA

  • when I do the attach and choose the mdf, and the ldf is on another drive, it does not always list an entry for the ldf. I know where the ldf is. I don't have an option to add it. that's why I think it has been empty and truncated. This has not happened to you?

  • ok - so why not script the attach

    EXEC sp_attach_db @dbname = N'mydatabase',   
    @filename1 =
    N'C:\mydatabase_Data.mdf',
    @filename2 =
    N'D:\mydatabase_log.ldf';

    It is 100 % not because it is empty and truncated, sql just can't find it .

    MVDBA

  • also check out "attach single file db" - but don't forget to clean up the old ldf files if you do use this

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-single-file-db-transact-sql?view=sql-server-ver15

     

     

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    what happened to good old fashioned command prompt tools

    dir /s *.ldf

    none of this fancy SQL malarkey 🙂

    Heh... I love it!  No PoSh required, either.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    MVDBA (Mike Vessey) wrote:

    what happened to good old fashioned command prompt tools

    dir /s *.ldf

    none of this fancy SQL malarkey 🙂

    Heh... I love it!  No PoSh required, either.

    it is very tempting to open up xp_cmdshell and pump the results into a table... then forget to disable xp_cmdshell (bad dba) - but it can be usefull occasionally (still being a bad dba)

    MVDBA

  • You may read these blogs: how to attach SQL database without LDF

    https://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

    https://www.stellarinfo.com/blog/attach-sql-database-without-transaction-log-file/

    SQL Database Recovery Expert 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply