Solved

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

Posted on 2008-06-26
2
210 Views
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?
0
Comment
Question by:stephi01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Expert Comment

by:dready
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.

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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