When you make a full database backup, the backup contains:
- from the data files, only the used data pages
- from the log files, the committed transactions that have not been written to the data files, and all uncommitted transactions
If you want the full backup to contain largely only of the data files, then you should ensure that you do not have any large uncommitted transactions at the time the backup ends.
Note that the sizes reported by the RESTORE FILELISTONLY command is the data and log file sizes at the time of the backup, not the sizes of the actual data contained in the backup.
E.g. Database A may consist of a data file that's 50 MB in size, and a trx log file of 25 MB. If all tables in database A were dropped, and there are no uncommitted transactions, a full database backup will come to only ~1 MB. Running RESTORE FILELISTONLY will still display a data file of 50 MB and a trx log file of 25 MB that needs to be restored.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software Beyond compression - SQL Backup goodies under the hood, updated for version 8
Comments
- from the data files, only the used data pages
- from the log files, the committed transactions that have not been written to the data files, and all uncommitted transactions
If you want the full backup to contain largely only of the data files, then you should ensure that you do not have any large uncommitted transactions at the time the backup ends.
Note that the sizes reported by the RESTORE FILELISTONLY command is the data and log file sizes at the time of the backup, not the sizes of the actual data contained in the backup.
E.g. Database A may consist of a data file that's 50 MB in size, and a trx log file of 25 MB. If all tables in database A were dropped, and there are no uncommitted transactions, a full database backup will come to only ~1 MB. Running RESTORE FILELISTONLY will still display a data file of 50 MB and a trx log file of 25 MB that needs to be restored.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8