Discrepancy in gigabytes between a bak file and restored database

  • Hello --

    I completed a database restore that appeared to be successful. The object here was to restore a bak file from a remote database server to a newly created database on the local server. However, when I was doing a follow-up check, I noticed there was a file size discrepancy between the bak file that was used for the restore, and the size of primary data file.

    When the restore was done, I utilized Management Studio, and ran through the steps listed below:

    1. Open SQL Server Studio, and select Databases from the left pane.

    2. Right-click on Databases, and select Restore Database from the drop down menu.

    3. Click on the From device radio button in the Source for restore section.

    4. Click on the button at the far right of the field, and the specify backup window will

    appear on-screen. Make sure the Backup Media type shown in the field is File.

    5. Click on the Add button, and navigate to the location of the desired backup file. The

    file in this example is:

    <database>.bak

    and is located at G:\BACKUP\Full Backups.

    6. Type in the name of the database in the To Database field in the Destination for restore

    section. Typing in a name that is different from the currently listed databases will

    cause SQL Server to create a new database during the restoration process. The name of

    the new database in this procedure is: <database>_AK.

    7. Click on the Options icon in the left panel to bring up the next page.

    8. Go to the Restore As field for the various parts of the database , and by right clicking

    on the button at the far right for each part, change the location of the restored files.

    The Rows Data, mdf, file should be restored here:

    Z:\DATA

    The Log, ldf, file should be restored here:

    L:\DATA

    The Filestream Data should be restored here:

    V:\FILESTREAM

    The location of the bak files is an iSCSI volume that is mounted by the local server. The volume is an export from a remote server where the bak file is located. The bak file is the latest in a series of image backups that have been run which create bak files bigger than the previous ones.

    How can I verify the database restoration is truly successful?

  • Why do you expect bak file size be equal to size of primary data file?


    Alex Suprun

  • I am a newcomer to database administration, with my expertise being in fileserver administration. When a restore is done in a filesystem, the restored file is typically the same size as that of the backup. My concern is based on that logic. I realize the rules are different in database administration, but I thought it would be a good idea to bring the subject up just confirm that I did not make a mistake in this case.

  • Estimate the Size of a Full Database Backup

    Before you implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. The backup operation copies the data in the database to the backup file. The backup contains only the actual data in the database and not any unused space. Therefore, the backup is usually smaller than the database itself.


    Alex Suprun

  • A database includes an .mdf file for data, an .ldf file for the transaction log, and potentially more .ndf files for data. There is free space in these files for normal operation and maintenance.

    The backup only includes the data.

    When you perform a restore, the database files are restored to the original size they were when the backup was taken.

  • Hello --

    I tested the database by connecting to it via a client application. The test was successful.

    I spoke with several colleagues about the discrepancy, and they made mention of the fact the file itself is not compressed, nor is there any page compression within the database. These can probably explain the difference.

  • kaplan71 (10/18/2012)


    Hello --

    I tested the database by connecting to it via a client application. The test was successful.

    I spoke with several colleagues about the discrepancy, and they made mention of the fact the file itself is not compressed, nor is there any page compression within the database. These can probably explain the difference.

    That and the fact that the freespace already mentioned doesn't get saved in the backup.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply