SQL Server Performance

Restoring Log Showing 2 Different Result.

Discussion in 'SQL Server Log Shipping' started by mmuneebahmed, Mar 17, 2004.

  1. mmuneebahmed New Member

    Hi everyone,

    I have been using this forum for the past 3 days for my log shipping problem. and I have read lot of articles and columns here and on different forums as well. But, couldn't find the answer to my problems. I hope this time I could find the answer. Thanx for everyone who provided their valuable thoughts and knowledge to me since then.

    Now here is my question.

    When I tried to restore the transaction log using the following command:

    Restore log ApplicationTEST
    from disk=''
    with
    standby=''

    gives me this, once the command is successful:

    Deleting database file 'D:SolomonApplicationTESTBackupLOGSUNDOUNDO_ApplicationTEST.DAT'.
    RESTORE LOG successfully processed 0 pages in 0.636 seconds (0.000 MB/sec).

    (Notice 0 pages processed)

    Now if I use the same above command to restore my transaction log adding the FILE option as follows:

    Restore log ApplicationTEST
    from disk=''
    with
    file=87,
    standby=''

    now gives me this:

    Deleting database file 'E:BackupsLOGSUNDOUNDO_Application.DAT'.
    Processed 109 pages for database 'Application', file 'Application_Log' on file 87

    (Notice the 109 pages processed)

    I'm using the same method on the same transaction log. This transaction log has many logs file=87 is just an example. I was expecting more than 109 pages to be processed once I just want to restore the whole file in one go.

    I'll appreciate any help in this regards.

    Thanx

    Muneeb.
  2. satya Moderator

    If you do not specify any filename to restore the TLog then a nominal SUCCESSFUL command will be produced.

    BTW how many Tlog backups existing to restore?
    What was the issue with Log shipping?
    Check error logs, log shipping monitor.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. mmuneebahmed New Member


    Hi Satya,

    Thanx for the reply.

    Actually I'm trying to apply my transaction log file to the database. Records are appending in the transaction log file. If I apply using the RESTORE LOG command it only shows the '..successful 0 pages' but if I restore manually by using FILE through Query Analyzer the results are different. I'm trying to apply my transaction log file in one go instead of restore backup sets one by one..is it possible or not using the RESTORE LOG command?

    Thanx.

    Muneeb.
  4. satya Moderator

    If you've full backup then you can do that, other wise to apply the sequential order you must follow the BACKUP LOG statement to apply sequentially to the database.

    Refer to books online for RESTORE topic which covers this information to use STOPAT clause. For eg:
    RESTORE LOG pubs
    FROM Pubs1
    WITH FILE = 4,
    STOPATMARK = 'RoyaltyUpdate'



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. mmuneebahmed New Member


    Thanx Satya, however I have one more question. Is there a way I can use the following command to capture all the information in a table.

    RESTORE HEADERONLY FROM DISK='<SomePathToMyTransactionLogs>'

  6. satya Moderator

    No easy task better to save results to a text file and use DTS to save it to table.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. mmuneebahmed New Member

    Thanx a lot Satya.

    I think my problem will solve if I can anyhow use the above command in my stored procedure and loop through the resultset and would able to read the position column instead of saving the result to a text file and use DTS to save the text file into a table and then loop. Is there any other way you suggest would be much easier or better.
  8. satya Moderator

    Thats a good approach with the available tools ensure you are upto the mark, I have no doubt on your idea.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. mmuneebahmed New Member

    <br />Here is the detail of my approach. Just wanted to share with others. Thanx for all the help.<br /><br />create table #header<br />(<br />BackupName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, -- Backup set name.<br />BackupDescription nvarchar(255), -- Backup set description.<br />BackupType smallint, -- Backup type:<br /> -- 1 = Database<br /> -- 2 = Transaction Log<br /> -- 4 = File<br /> -- 5 = Differential<br /> -- Database<br /> -- 6 = Differential File<br />ExpirationDate datetime, -- Expiration date for the backup set.<br />Compressed tinyint, -- SQL Server does not support software compression.<br />Position smallint, -- Position of the backup set in the volume (for use with the FILE = option).<br />DeviceType tinyint, -- Number corresponding to the device used for the backup operation.<br />UserName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, -- Username that performed the backup operation.<br />ServerName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, -- Name of the server that wrote the backup set.<br />DatabaseName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, -- Name of the database that was backed up.<br />DatabaseVersion int, -- Version of the database from which the backup was created.<br />DatabaseCreationDate datetime, -- Date and time the database was created.<br />BackupSize numeric(20,0), -- Size of the backup, in bytes.<br />FirstLSN numeric(25,0), -- Log sequence number of the first transaction in the backup set. NULL for file backups.<br />LastLSN numeric(25,0), -- Log sequence number of the last transaction in the backup set. NULL for file backups.<br />CheckpointLSN numeric(25,0), -- Log sequence number of the most recent checkpoint at the time the backup was created.<br />DatabaseBackupLSN numeric(25,0), -- Log sequence number of the most recent full database backup.<br />BackupStartDate datetime, -- Date and time that the backup operation began.<br />BackupFinishDate datetime, -- Date and time that the backup operation finished.<br />SortOrder smallint, -- Server sort order. This column is valid for database backups only. Provided for backward compatibility.<br />CodePage smallint, -- Server code page or character set used by the server.<br />UnicodeLocaleId int, -- Server Unicode locale ID configuration option used for Unicode character data sorting.<br />UnicodeComparisonStyle int, -- Server Unicode comparison style configuration option.<br />CompatibilityLevel tinyint, -- Compatibility level setting of the database from which the backup was created.<br />SoftwareVendorId int, -- Software vendor identification number.<br />SoftwareVersionMajor int, -- Major version number of the server that created the backup set.<br />SoftwareVersionMinor int, -- Minor version number of the server that created the backup set.<br />SoftwareVersionBuild int, -- Build number of the server that created the backup set.<br />MachineName nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />, -- Name of the computer that performed the backup operation.<br />Flags int, -- Bit 0 (X1) indicates bulk-logged data is captured in this log backup.<br />BindingID uniqueidentifier, -- Binding ID for the database.<br />RecoveryForkID uniqueidentifier, -- ID for the current recovery fork for this backup.<br />Collation nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> -- Collation used by the database.<br />)<br />insert #header<br />exec ('<br />RESTORE HEADERONLY <br />FROM DISK = ''F:ftprootHPSQLTRIXwwwdtrix20040318.bac''')<br /><br />If I loop the #header table then I'll everytime know how many backupsets are in the transaction log and then I can automatically access the most recent ones.<br /><br />Muneeb.<br /><br /><br />
  10. satya Moderator

    Appreciate your interest in posting the solution too.[8D]

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page