Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Discrepancy in gigabytes between a bak file and restored database


Discrepancy in gigabytes between a bak file and restored database

Author
Message
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 324
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?
Alexander Suprun
Alexander Suprun
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 1516
Why do you expect bak file size be equal to size of primary data file?


Alex Suprun
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 324
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.
Alexander Suprun
Alexander Suprun
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 1516
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18741
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
My Blog: www.voiceofthedba.com
kaplan71
kaplan71
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 324
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45062 Visits: 39904
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search