October 17, 2012 at 1:53 pm
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?
October 17, 2012 at 2:42 pm
Why do you expect bak file size be equal to size of primary data file?
October 17, 2012 at 2:51 pm
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.
October 17, 2012 at 3:05 pm
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.
October 17, 2012 at 3:36 pm
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: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 18, 2012 at 2:10 pm
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.
October 18, 2012 at 4:09 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply