Database Import Size is 4X Original

This isn't so much a problem yet as a concern, and the hopes of avoiding a problem down the road.

We have a production application that uses a DB on MS SQL 2008.  The DB is about 11GB.  We are moving to a new version of the application but it requires MS SQL 2012.  From the newly setup MS SQL 2012 server I did an import of the production database, which completed successfully though it took 90 hours.  Once complete I did a backup, shrink DB, shrink file, and the new DB is 49GB.

I deleted the DB and tried the process a second time with the same results.

I haven't finished setting up the application, but don't want to expend a bunch of time and effort if there might be an underlying problem.

So the question is, would it be normal for a DB to expand that much with an import (SQL 2008 to 2012)?  I am not a DBA, but the size difference seems pretty exceptional.

Thank you for any guidance you might have.
rssystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
NO, that's definitely not normal, it's quite extraordinary, including the huge time taken.

Note, though, that the log file must be fully restored.  If the log is over-sized, shrink it before copying the db.

Was the original db compressed and the new one not?
How did you do the import?
Can you instead make a backup, copy the backup to the new server, then restore / CREATE DATABASE ... FOR ATTACH the backup?
rssystemsAuthor Commented:
Prior to the Import I did a full backup to clear transaction logs, and did a shrink of both DB and files.  So it was clean prior to the import.

It is not compressed, just on a clustered server drive presented from an iSCSI LUN.

I am using the MS Import/Export utility.  I didn't want to copy/attach or backup/restore since it will just be a 2008 DB running on a 2012 server.  I was assuming the import/export utility made some changes to the DB so it isn't running in a compatibility mode.
Scott PletcherSenior DBACommented:
So you backed up the log file and then shrank the log file prior to the export/import?  Yes, in that case, the log file shouldn't be the issue.

I don't know if the I/E utility changes the compatibility or not.  But it's definitely way too slow for me to use.  You can specify the compatibility yourself after the db as restored.

RESTORE DATABASE db_name ... FOR ATTACH;

ALTER DATABASE db_name SET COMPATIBILITY_LEVEL = 110;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Prior to the Import I did a full backup to clear transaction logs
Just to restate Scott's point.  A Full backup does not clear the Transaction Log.  Period.
rssystemsAuthor Commented:
The log file wasn't an issue.  prior to backup and shrink DB was 18 GB log was 18 GB on prod server.  After they were DB 11 GB, and log 1 GB.  Then I did the Import/Export utility to the new server and DB was 49 GB, and log was 1 GB (266 million rows transferred).

The instructions for the application install/upgrade call for an import/export, but if I can set the database compatibility level and nothing special is done during the import/export process I will copy the files over and attach them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.