Restored database file size is small 1

[BKer]

I have restored a database from a .bak file that is 50 megs and the resulting database is SQL Server is now 14 megs and only contains about a third of the data that was in the database previoiusly. Is there a reason why something like this would happen? What can I do to get my data back? Thanks in advance.

Brian Kelleher

 

[tlbroadbent]

A BAK file can contain multiple backups. That could explain the difference in size. You can verify the number of backups by reading the headers on the backuip file. See thread183-485966 for code to do that. You can also look in the msdb.dbo.sysbackupfile table to see the size of the backup that are on the file. This should help you determine the actual DB size.

You say 2/3 of the data is missing so it doesn't appear to be a case of multiple backups on the same file. Were any error messages dispalyed while RESTORING the file? If you are able to get into the database, it would seem that the restore worked fine. Perhaps, there were transaction backups that were not restored.

I recommend that you check out the backup sizes and try to confirm that the database should be 3 times larger.
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[BKer]

tlbroadbent,

I executed the code you supplied to get header information and it returned two rows. The first row appears to be an older backup which may explain the limited data and also has my old login information (13megs whichis the size of DB I have restored). The second contains what would appear to be the lastest backup and my new login which I used to backup the DB recently(36megs). The only reason I said 1/3 was because it contained two months of info for a six month period. There were no error messages when I restored. So it looks like there are multiple backups on the same file. If there are two different backups then how do I retrieve the one I need?

Thanks,
Brian Kelleher

 

[tlbroadbent]

You can indicate which file to restore by using the WITH FILE = file_number option. The syntax would be similar to the following. See SQL BOL for more details.

RESTORE DATABASE YourDatabase
FROM DISK = 'E:\MSSQL\Backup\YourDatabase.bak'
WITH FILE = 2
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[BKer]

Terry,

Thanks for your help I got everything back and learned a lot in the process.

Brian Kelleher