Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Unanswered: Retreiving TranLog Filenames from Windows Directory

    Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006).

    Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available.

    Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand?

    Thanks,

    Tom

    -- This is all just a Figment of my Imagination --

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    They have nothing since 1/6/2006.

    This sounds like a really nasty situation. Are all of the trannies in one directory? I do have some vbscript that recursively searches directories and subdirectories using the filesystem objects but it is at home. I imagine if you altered this code to sort the results by date created you can use the results that are stored in a table to create your transaction log restore scripts. Can you hold out for 4 hours?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Thanks Sean.

    Nope ... good ole' NetBackup. But this is human error ... somebody changed the rules so not one of the daily full db backups have been captured since 10/01/2006. We keep 2 days of full and tran on the prod machine before they are suppose to go to tape. So even though we haven't been flying without a net, it's been damn close. That's what happens when you are acquired by another company and become the red-headed step-child

    The full database restore will take about 10 - 11 hours to complete. It's not worth an all nighter for a dev machine, but I need to automate the tran restore and hope there is no file corruption on the tran backups.

    Whatever you have, I'll look at and adapt as needed. If it works, i'll post it on SQLKit when we get the solutions area set up. Feel free to post here or check my profile for an email address.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I will send you code as soon as I get home. I am doing some db open heart surgery on a client site in about 1/2 hour but I should get back to the house by 7. As I recall, you feed this thing a file path and it dumps the file name, date created and the path of every file in that path and every sub folder in that path until it drills all the way to the bottom and it stores all of this information in a database table.

    From there you just write a little code that writes a lot of code and you are home free.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Being really low down and high smelling, I like the easy answer:
    Code:
    CREATE TABLE #cmdresult (
       t		VARCHAR(1000)
       )
    
    INSERT INTO #cmdresult (t)
       EXECUTE master.dbo.xp_cmdshell 'dir c:\windows'
    
    -- SELECT * FROM #cmdresult
    
    SELECT SubString(t, 40, 100)
       FROM #cmdresult
       WHERE t LIKE '[0-9][0-9]/[0-9][0-9]/[0-9]%'
          AND t NOT LIKE '%<DIR>%'
    
    DROP TABLE #cmdresult
    -PatP

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Sorry for the delay. Got home later than expected. Made dinner. 45 minutes on the exercise bike, shower etc...

    Code:
    Sub main()
    
    Dim con,sql,path
    Set con = CreateObject("ADODB.Connection")
    con.Open "Provider=SQLOLEDB; Server=SQLServer; Initial Catalog=ALEX2001; Integrated Security=SSPI"
    
    sql = "EXEC ALEX2001.dbo.DP_Truncate_SGMLInventory"
    con.Execute (sql)
    
    path = "\\ALEXSTR01\ALEXSTR-Data\TECH_BOOK_Deliveries\"
    Call Search(path)
    path = "\\ALEXSTR01\ALEXSTR-Data\APEX_Deliveries\"
    Call Search(path)
    
    sql = "EXEC dbo.Update_DP_ProductionTracking_From_DP_SGML_Inventory"
    con.Execute(sql)
    con.Close
    Set con = Nothing
    
    End Sub
    
    Function Search(x)
        Dim con2,sql,Item,Item2,DriveSpec,f,fc    
        DriveSpec = x
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set f = fso.GetFolder(DriveSpec)
        Set fc = f.Files
        Set con2 = CreateObject("ADODB.Connection")
        con2.Open "Provider=SQLOLEDB; Server=SQLServer; Initial Catalog=ALEX2001; Integrated Security=SSPI"
    
        For Each Item In fc
           sql = "EXEC DP_InsertIntoDP_SGML_Inventory @Location = '" & DriveSpec & "',@TheFile = '" & Item.Name & "',@DateCreated = '" & Item.DateCreated & "';"
           con2.Execute (sql)
        Next 
        con2.Close
        Set con2 = Nothing
        If f.SubFolders.Count > 0 Then
           For Each Item2 In f.SubFolders
                Call Search(DriveSpec & Item2.Name & "\")
           Next 
        End If
        Set fso = Nothing
        Set f = Nothing
        Set fc = Nothing
        Set Item = Nothing
        Set Item2 = Nothing
    End Function
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •