Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively.
My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) sized backup file, or does it only contain mdf backup?
I need to know this since I don't have this much of space on my hard disk.
I'm using SQL 2008 R2 standard edition
A full backup will contain all used pages in both files (you can convince yourself of this by restoring a full backup: the new files end up the same sizes with the same amount of content, which means the backup must have contained the information required to achieve that). Both files, especially the transaction log, will contain block that are allocated from the filesystem but not currently in allocated to an object in the database so this will be smaller than the combined size of the actual files.
To get an indication of the size of a full backup, run EXEC sp_spaceused on that database. For example on one of my databases this returns database_size = 70015.81 MB and unallocated_space = 2768.83 MB so a full backup will be approximately 67247 MB in size. With a log the size of your example I expect that unless you've not taken any backups recently there will be a log of space unallocated in the log files that therefore won't affect the backup size.
If you are using SQL Server 2008R2 or later (or 2008 Enterprise Edition) then you can compress the backup with the WITH COMPRESSION option which will generally make it much smaller. This will take CPU time of course, but may actually make the process of taking the backup faster by reducing the amount of IO involved.
Does .bak file also contain backup of ldf file?
No it does not contains backup of ldf file. It might contain some information not the complete backup.
A full or differential backup contains enough log to be able to recover the database to a transactionally-consistent view of the database at the time the data-reading portion of the backup finished (or as far back as the oldest log record that transactional replication has not yet processed – to ensure that replication works properly after a restore).
My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) sized backup file, or does it only contain mdf backup?
It will surely not result into 355G because full backup includes 2 things
Complete data present in data file, no free space would be included into the backup
A small amount of transaction log to bring database to a consistent state when this backup is restored.
Paul has explained in his article how much transaction log does full backup includes
For more learning
TechNet article about Backup internals
Depending on edition of SQL Server you are using you can use backup compression to further reduce backup size.
