Sql-server – Does .bak file also contain backup of ldf file

backupsql serversql-server-2008-r2

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

Best Answer

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.

Related Question