Can you let me know why a transaction log would be a lot smaller in size after restoring from a full backup. Currently we have a massive log file on the live server, but after a restore to a test / development server the log file is considerably smaller. Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.
but after a restore to a test / development server the log file is considerably smaller
Because when you're creating a full backup you're creating a backup of the data (as it is physically and as it is materialized from the log). Restoring it somewhere else restores it as data, not log.
Im guessing this is because the log was truncated as part of the backup however why would the original log file still be so huge.
The original log file might be truncated so the space can be reused, but it's size might stay the same.
A full database backup contains almost no transactions - just the transactions that happened while the FULL transaction log was being created. The LDF file created when a database is restored using a full database backup is almost empty, regardless its size. It should be inflated (with no data but active portion of transaction log) to its backed up size when restoring.
If you want to restore the transaction log file too, the best way to do that is to restore the database using its LDF and LDF files (attach them), instead of using a full database backup.
There are many reasons why the original file is huge:
- The database is in FULL recovery model and you don't create transaction log backups at all
- The database is in FULL recovery model and you don't create transaction log backups often enough
- there are large-scale index operations
For any of the possible causes, creating transaction log backups regularly should keep the size of the LDF file.
After Backup the logs are taken back to the start as the database is newly recorded Try Reading this link it may help you the : http://msdn.microsoft.com/en-us/library/ms190440.aspx
Read How a log backup are used