Tuesday, November 1, 2011

Log file size after backup and restore

Quick summary of the post:

A restoration of a full database backup retains the log file size before restoration.

Now for the details :

Consider a large database that you want to move from one server to another. Assume that the log file of the source database is huge. For taking a backup, the size of the log file doesn't matter as a backup operation always backs up only the used pages of database.After restoration, the restored database's log file size is same as the original database, even though the backup file used for restoration
is much smaller. If one has a space constraint in the destination server, then its better to shrink the log before taking the backup of the original

Let us take the sample database dbadb . The database size is 107 MB with data file size being 16 MB and log file size being 91 MB. A full backup file size is only 3.2 MB

Database size

Full Backup size

A restore of the backup will create a database again at the original size of 107 MB with data and log file sizes being 16 MB and 91 MB respectively.

Restore of Database

Database size of restored database

So, if your destination server doesn't have enough space, then ensure your log file size is small before taking the full backup.