SQL Database shrinks after restore into SQL 2005. Is this normal?

Posted on 2008-06-26
Medium Priority
Last Modified: 2010-03-19
I have two DB backups :
DB a is 54345K
DB b is 456130K

I restore these DB into SQL 2005 and the Physical Files are:
DB a.mdf- 146048k
DB a.ldf- 399104k
DB b.mdf- 504768k
Db b.ldf- 387968 k

I back these Databases up:
DB a is now: 44299k
DB b is now: 292132k

Is this normal?
Question by:stephi01
LVL 11

Expert Comment

ID: 21878274
I cannot find a link that proves it, but i assume that MS will definitely have improved the compression technology for backups between 2 versions. There are quite some 3rd party tools for improving the size of the backups, as databases do grow very large. (see http://www.sqlmag.com/Article/ArticleID/98180/sql_server_98180.html) So there is absolutely place for improvement for MS, and so i would not worry about this at all; Sounds very logical to me.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 21878689
this is perfectly normal.
explanation: the database backup contains the active transaction log part, which could be quite big.
after a restore, that active part becomes clear automatically, as it is rolled back (ie rolled forward) during the recovery part of the restore.
so, a backup just after the restore will have close to 0 active transaction log to be included in the backup, hence can be smaller.

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Office 365 has multiple features and services which are specially designed to help businesses to reduce their dependence on on-premises IT resources. It also offers great flexibility and enhanced security. But like any other data, Office 365 mailbo…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question