Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Restore compressed backup creates 5x larger database Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 9:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 39,886, Visits: 36,233
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 2008, MVP
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

Post #1555022
Posted Wednesday, March 26, 2014 9:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 986, Visits: 1,327
See attached:

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

http://www.linkedin.com/in/kurtwzimmerman


  Post Attachments 
Compressed_Non_Compressed_20130326.xlsx (41 views, 12.88 KB)
Post #1555054
Posted Wednesday, March 26, 2014 11:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 450, Visits: 1,430
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 ?
Post #1555094
Posted Wednesday, March 26, 2014 11:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 39,886, Visits: 36,233
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 2008, MVP
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

Post #1555119
Posted Thursday, March 27, 2014 7:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 986, Visits: 1,327
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
Post #1555426
Posted Thursday, March 27, 2014 10:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,102, Visits: 3,164
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1555530
Posted Friday, April 4, 2014 9:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:30 AM
Points: 1,422, Visits: 1,838
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
Post #1558694
Posted Thursday, April 17, 2014 8:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 986, Visits: 1,327
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!

Kurt


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

http://www.linkedin.com/in/kurtwzimmerman
Post #1562688
Posted Thursday, April 17, 2014 10:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:30 AM
Points: 1,422, Visits: 1,838
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
Post #1562909
Posted Tuesday, April 29, 2014 11:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 3:23 AM
Points: 101, Visits: 419
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?
Post #1566067
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse