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
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1398
Greetings all;
I've been trying to find some information about what might be going wrong here. I'll be the first to admit pilot error.

I have a production database that has 15.357 gb initial data size and is showing 27% available free space. The log has a 27.670 gb initial data size and since the recovery model is FULL is showing 99% available free space.

I run nightly backups along with transaction log backups every 15 minutes. Both the backup and transaction logs are being save by the native compression.

I'm running Enterprise Edition (64-bit) in a VM environment.

Here is my problem. I restore the backup to my UAT server and the initial data size explodes to 53gb and the log is set to 1gb. I am creating the restored database at the time of restore and not loading the restore on top of an existing database.

If I take a backup of the production database without compression the restore (same process) creates the initial sizes of data and log.

Has anyone else noticed this issue? Is this a known bug? You input is greatly appreciated.

Kurt Zimmerman

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

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
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?

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


SQL Guy 1
SQL Guy  1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 2506
In order to find the reason, run RESTORE FILELISTONLY from the same source which you use for actual restore. It will show all file sizes in bytes
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1398
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

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

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
Hmmm... that's odd.

Should created the DB exactly as it was at the time of the backup. Nothing else going on? No reverts from snapshot? No data obfuscations being run?
Can you repo this on a different 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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1398
GilaMonster (3/26/2014)
Hmmm... that's odd.

Should created the DB exactly as it was at the time of the backup. Nothing else going on? No reverts from snapshot? No data obfuscations being run?
Can you repo this on a different database?


Nothing else going on, no snapshots, and no data obfuscations being run. Let me try on another database.

Kurt

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

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
What are the exact versions of the two servers?
SELECT @@Version

Any traceflags enabled on either server?

There's only one backup in that file? Not multiple appended somehow?

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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1398
GilaMonster (3/26/2014)
What are the exact versions of the two servers?
SELECT @@Version

Any traceflags enabled on either server?


no traceflags.

Production server:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)



UAT server:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)



I performed this test on another database in the same environment and I am having the same issue.

Kurt

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

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86116 Visits: 45229
You're absolutely sure there aren't somehow multiple backups appended in the same file? (since you're using noinit on the backup)

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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 1398
GilaMonster (3/26/2014)
You're absolutely sure there aren't somehow multiple backups appended in the same file? (since you're using noinit on the backup)


Absolutely!

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

http://www.linkedin.com/in/kurtwzimmerman
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