Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Discrepancy in gigabytes between a bak file and restored database Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 1:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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?
Post #1374033
Posted Wednesday, October 17, 2012 2:42 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:10 PM
Points: 222, Visits: 1,027
Why do you expect bak file size be equal to size of primary data file?


Alex Suprun
Post #1374057
Posted Wednesday, October 17, 2012 2:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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.

Post #1374061
Posted Wednesday, October 17, 2012 3:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:10 PM
Points: 222, Visits: 1,027
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
Post #1374065
Posted Wednesday, October 17, 2012 3:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1374069
Posted Thursday, October 18, 2012 2:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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.

Post #1374582
Posted Thursday, October 18, 2012 4:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1374601
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse