Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Unanswered: Restore Transaction Log Backups

    Hy Guys,

    I have a problem with restore Log.

    We make a FULL backup everyday at 22:00.
    Transaction LOGs Backups are made at every 30 minutes (all the day) (WITH NO INIT) - One Log Backup per Day

    I restored a a FULL Backup database from 10/17/2002 with this code:
    Code:
    RESTORE DATABASE dbTest
    from disk = 'd:\MSSQL7\BACKUP\DB_20021017_dbsolomonprodapp.bak'
    WITH norecovery
    GO
    Now I need to restore Transactions until 10/18/2002 16:30.
    So, I have to restore
    Lg_20021017_dbSolomonProdApp.bak
    AND
    Lg_20021018_dbSolomonProdApp.bak (Until 16:30)

    My problem is.. How to make this Log Restore???
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post

    Q1 My problem is.. How to make this Log Restore???
    A1 Sequentially (in your case, by position similar to when restoring from tape, using time constraints as necessary). I suggest the following:
    i I find it best to Dump to individual (dump) files. (multiple reasons)
    ii Doing so also makes it clearer to see what you are doing, for example:

    Restore Database
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_db_200210250 830.Bak'
    With Stats = 1, Replace, NoRecovery,
    Move 'dbSolomonProdApp_Data' To 'c:\Test_dbSolomonProdApp_Data.mdf',
    Move 'dbSolomonProdApp_Log' To 'c:\Test_dbSolomonProdApp_Log.ldf'

    --> Sequential dTL Restore Statements:

    --> dTL file: 1
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50843.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 2
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50913.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 3
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50943.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 4
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51013.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 5
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51043.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 6
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51113.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 7
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51143.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 8
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51213.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 9
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51243.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 10
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51313.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 11
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51343.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 12
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51413.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 13
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51443.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 14
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51513.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 15
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51543.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 16
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51613.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 17
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51643.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 18
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51716.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 19
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51743.Trn'
    With Stats = 1, NoRecovery

    --> dTL file: 20
    Restore Log
    [Test_SolomonProdApp]
    From
    Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51813.Trn'
    With Stats = 1, NoRecovery

    Restore DataBase
    [Test_SolomonProdApp]
    With Recovery

  3. #3
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Thanx!

    Hy DBA,
    Your answer works perfect. But i was with my transaction logs in one file. Then i made like this:

    Code:
    -- This command returns many important information about this backup.
    -- Like the date of the backup sets and their numbers.
    RESTORE HEADERONLY FROM  DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'  WITH   NOUNLOAD
    GO
    
    --Restoring the last Full Backup
    RESTORE DATABASE dbSolomonTeste
    from disk = 'd:\20021016_dbsolomonprodapp.bak'
    WITH norecovery
    GO
    
    
    --Now I recovery all backup sets in the Log backup
    RESTORE LOG [dbSolomonTeste] 
      FROM  DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' 
      WITH  FILE = 1,  
      NOUNLOAD ,  
      STATS = 10,  
      NORECOVERY 
    GO
    
    RESTORE LOG [dbSolomonTeste] 
      FROM  DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' 
      WITH  FILE = 2,  
      NOUNLOAD ,  
      STATS = 10,  
      NORECOVERY 
    GO
    
    RESTORE LOG [dbSolomonTeste] 
      FROM  DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' 
      WITH  FILE = 3,  
      NOUNLOAD ,  
      STATS = 10,  
      NORECOVERY 
    GO
       .
       .
       .
    
    RESTORE LOG [dbSolomonTeste] 
      FROM  DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' 
      WITH  FILE = 48,   --In my Case, the last backup set i needed!
      NOUNLOAD ,  
      STATS = 10,  
      NORECOVERY 
    GO


    Thank you for helping me!!
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    RE: Thank you for helping me!!

    You are welcome.

    As I alluded to, I generally avoid monolithic TL dump files.
    Some pain I've experienced with them includes:
    i) You want to move it elsewhere to use it, but cannot because it has become huge (either connectivity loss, corruption, etc., occurs during attempted file transfer processes, or the target only has sufficient free disk space on multiple smaller volumes).
    ii) RESTORE HEADERONLY results take FOREVER, and fails before the complete header result set is returned (fortunatly, the needed TL dumps on it were usable).
    iii) It (a single monolithic TL dump file) gets corrupted, and the whole thing is useless! (That can be extremely painful for an organization, and is the main reason I implement and / or encourage the use of individual dump files.) While I have seen this happen on a Windows OS fileserver, it seems to happen most frequently on third party NAS boxes, (especially older SNAP storage servers), with large monolithic TL dump files. I don't recall ever seeing it happen on a local data storage volume, however; so, if that is your situuation, you may have relativly little (corruption) risk in using monolithic TL dump files.

Posting Permissions

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