SQL Server Performance

Transactional Backup (Beginner)

Discussion in 'ALL SQL SERVER QUESTIONS' started by Antonio Benildus Muerling, Apr 30, 2012.

  1. Hi All,

    Can someone help me in telling how do I restore a transactional backup to a database, and the main question I have is, when I restore the transactional Database, would the transaction backup records would be there on top of the older records, or would it overwrite the Database entirely, as a result of restoring the transactioinal backup file.

    Thanks,

    AB
  2. Daxesh New Member

    To restore transaction log backup,
    - first you have to restore full backup WITH NORECOVERY option
    - restore all transaction log backup (except the last one you want to restore) WITH NORECOVERY option
    - restore last transaction log backup WITH RECOVERY option

    So in true sense, you are replacing your current ONLINE database from backup (FULL + TLog backups)
    You cannot restore transaction log backup on existing ONLINE database

    I hope this will help

    Daxesh
  3. Hi Daxesh / all,

    Many thanks on your information, I could do as per to what is meant by above, I did not understand the part you meant by, "you are replacing current ONLINE database from backup, and you cannot restore log backup on existing ONLINE database" kinda got confused with current and existing as it has the same meaning

    and finally the basic quesion that I asked from you about the transactional log, when you restore it, on a database would it replace it entirely on it, it would be easier if I put scenarios and ask from you

    Scenario1

    There is a working database with so many records, and lets say it has 5000 mb worth of records, and I am doing only a transaction log restore worth of 300mb, so my question is, as a result of restoring only the transaction log backup, would the database eventually have 5300 mb worth of records, or 300 mb worth of records, is it possible to do a transaction log backup alone.

    Scenario2

    the existing working database is 5000mb, and I have got a full backup worth of 5000mb, and transaction log backup of 300 mb (only one transaction log is available), and I am restoring the database in the same way as you mentioned
    restore FULL BACKUP wth no recovery option
    restore last transaction log backup with recovery option

    now what would be the size of the database after the above steps, is it 5300 mb

    Thanks

    AB
  4. Daxesh New Member

    Scenario 1 is not possible, you cannot restore transaction log backup alone.

    Even you cannot take transaction log backup if you have not taken atleast one full backup.

    You can try this in test environment,
    Change database recovery model from FULL (or Bulked Logged) to SIMPLE and then change it back to FULL.
    After this you will not be able to take transaction log backup untill you take a full backup.

    Scenario2 is the only option if you want to restore data from transaction log backup
  5. Shehap MVP, MCTS, MCITP SQL Server

    Dear Antonio Benildus,

    It sounds like you are seeking DB sizes while different restore scenarios that let me elaborate it for you :

    · DB Full backup is for .mdf +.ndf + .ldf files

    · DB Differential backup is for .mdf +.ndf

    · Transaction log backup is for .ldf but either is to truncate log file (Default) or to keep it without truncate

    Thereby, if Full is 5000 MB and transaction log backup is 300 MB , DB size isn't must to be 5300 MB since Full backup might already contain a transaction log of 300 MB….

    Moreover, this accumulation of transaction backup sizes is invalid if multiple transaction log backup are there due to truncate factor

Share This Page