Transactional Backup (Beginner) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transactional Backup (Beginner)

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
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
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
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
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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |