SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore compressed backup creates 5x larger database


Restore compressed backup creates 5x larger database

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211260 Visits: 46255
Just to be sure to be sure to be sure :-) (because it would be a cause of this), what does RESTORE HEADERONLY return on one of the backups showing this behaviour?

Edit: Also, any jobs running on the UAT server which could change file sizes? Any DDL triggers?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1400
See attached:

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Attachments
SQL Guy 1
SQL Guy  1
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3422 Visits: 2592
Kurt W. Zimmerman (3/26/2014)
GilaMonster (3/26/2014)
You said the production DB has initial size of 15GB, what's the actual size of the data file on disk?

Edit: Native backups? What's the backup command? What's the restore command?


15GB

This is the backup command:

BACKUP DATABASE [xxxx]
TO DISK = N'D:\SQLDumps\xxxx_backup_<datetime stamp>.bak'
WITH NOFORMAT
, NOINIT
, NAME = N'xxxx_backup_<datetime stamp>'
, SKIP
, REWIND
, NOUNLOAD
, COMPRESSION
, STATS = 10


This is the restore command:
RESTORE DATABASE [xxxx]
FROM DISK = N'E:\Transfer\xxxx_backup_<datetime stamp>.bak' WITH FILE = 1
, MOVE N'xxxx_dat' TO N'D:\SQLData\xxxx.mdf'
, MOVE N'xxxx_log' TO N'E:\SQLLogs\xxxx_1.ldf'
, NOUNLOAD
, STATS = 10

Kurt


Did you try WITH REPLACE ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211260 Visits: 46255
Officially weird

For completeness (and because I'm stuck for ideas), can you post the results of a RESTORE FILELISTONLY on both backups, as well as the output of a SELECT * FROM sys.database_files on the source database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1400
I have production issues I need to deal with and will post your requested information as soon as I can free up.
Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18574 Visits: 7400
I restore the backup to my UAT server and the initial data size explodes to 53gb and the log is set to 1gb.


Hmm, I think the log file should be the same size as it initially was when it's restored. Are there some other commands running on the db after it is restored? Maybe to reorg/rebuild the data and/or shrink the log?!

[Btw, 53GB is only ~3.5x 15GB, not 5x :-)).

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 2149
It is wierd. This is just a guess - Could it be that the recovery model may be changing which is causing the log to flush over to the data files and drop down to 1GB itself?

Another thing is that although the database is restored on a SQL 2008 R2 environment, it appears that the database is running under the SQL 2005 compatibility mode (90). Not sure if backup compression on a database with compatibility level 90 has something to do with it.

I don't have a SQL instance right now to test it on, but will give it a shot as soon as I have one.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1400
OK... well I have had some more time to look at this issue. What I did was to run a Disk Usage by Table report on 2 databases, my production database and a copy of that same database created from a SQL Compressed backup.


From my discovery the RESERVED (KB) along with the DATA(KB) increased especially with some of the larger tables.

However when I restore the database from a backup that doesn't utilize SQL Compression I have a database close to the original size of the production (original) database.

Still scratching my head! Crazy

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 2149
Just thinking out loud (not prototyped/tested) - is it possible that both servers have different hard drive partition configuration when they were formatted as part of setting up of the servers (sector sizes, etc)?

I'm guessing the when restoring from a compressed backup, a different drive configuration has some role to play in the final size.

By the way - closing one of my action items from a previous comment - I was able to confirm that the compatibility level has nothing to do with this issue.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
timr-902336
timr-902336
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 456
Greetings!

Did you inherit this UAT server or build it yourself?

Before going mad just restore it elsewhere, if successful and the result is a perfect copy of production, you can rule out any troubles in production and focus on the one UAT server. <-- how much time do you wish to waste if its the UAT server or are you determined to find out what's going on?, how long to rebuild?

Have you run dbcc checkdb on a restored copy on the UAT server?

Offlined sql and run check disk at the os level?

Maybe run a profiler trace on the restore?
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