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 7:55 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: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1554957
Posted Wednesday, March 26, 2014 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 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 #1554968
Posted Wednesday, March 26, 2014 8:08 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 423, Visits: 1,315
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
Post #1554970
Posted Wednesday, March 26, 2014 8:25 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: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1554981
Posted Wednesday, March 26, 2014 8:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 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 #1554989
Posted Wednesday, March 26, 2014 8:35 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: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1554996
Posted Wednesday, March 26, 2014 8:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 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 #1555004
Posted Wednesday, March 26, 2014 8:56 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: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1555011
Posted Wednesday, March 26, 2014 8:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 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 #1555014
Posted Wednesday, March 26, 2014 9:05 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: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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
Post #1555018
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse