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

Question

Post reply

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

afw

SSC Enthusiast

Points: 151
More actions
November 1, 2019 at 2:40 am

#3693728

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?

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

what happened to good old fashioned command prompt tools

dir /s *.ldf

none of this fancy SQL malarkey 🙂

MVDBA

afw SSC Enthusiast Points: 151 More actions · Answer 4

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?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

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

prettsons SSCertifiable Points: 7453 More actions · Answer 9

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 🙂