0

I am using a Microft SQL Express edition server limited to 10GB/database. The original database runs on a licenced server and it is ~15GB. I was given a small part of this database (last couple years of data) which should be well below 10GB. The .bak file is 6GB, I am assuming the DB itself is ~2.5-3GB. When I try to restore from the backup the MSSQL Management studio clearly sees that the .bak file is 6GB but still it says "Restore failed, resulting database would exceed you limit of 10GB/database".

Any ideas what is happening? I am confused.

My "minimal" .bak file resulted from cutting down (DELETE a few years of data) a copy of the original database.

1

Louis,

Even though the file itself is only 6 GB, the database (when restored) would have a size LARGER than 10 GB. Ask whomever gave the backup to you to shrink the database size down to under 10 GB and make you another backup. Even if there isn't any data in the database, it can't have a size larger than 10 GB.

| improve this answer | |
  • TO be exact: They deleted data but left the database file size there. And that is being checked. Now why you arel imited to a Express is a good question - because if you do development then the developer edition is pretty much free and identical techncially to the enterprise version. – TomTom Nov 1 '14 at 18:14
  • Thanks both. Downloaded the developer edition and found some shrink option in there. Although I am pretty sure the .mdf we backed up was less than 3GB, somehow the .mdf created when I restore on my PC is 15GB. So I am guessing a shrink on my copy on the licensed server will do the job. – Louis Papaloizou Nov 1 '14 at 19:03
1

The .bak file is 6GB, I am assuming the DB itself is ~2.5-3GB.

THat would mean that there is a NEGATIVE COMPRESSION and the backup is larger than the original. You seriously believe that? I have some land on the moon to sell to you.

6GB means the database is larger than that. Can be 10, can be 10000gb - because the backup only has the pages that contain data, while the FILES (and the original file size is being restored AND is part of the check in Express) can mostly be empty.

| improve this answer | |
  • Thought it includes the .ldf log in there which sometimes gets huge. – Louis Papaloizou Nov 1 '14 at 19:10
  • @LouisPapaloizou Which would turn any sort of full backup useless. – TomTom Nov 1 '14 at 19:20
  • Hey! I happen to own a piece of land on the moon. Even on the earth-near side. Now can you sell me this negative compression program? It certainly sounds most interesting. I could use it recursively to create a pile of data big enough to just walk up to my moon estate. – the-wabbit Nov 1 '14 at 19:20
  • OK guys. Let's take things one by one. The .mdf file is the DATABASE file and the .ldf the LOG file, correct? I definitely remember attaching a database just by copying the .mdf in the past. So when it says > 10GB it refers to the .mdf. Correct? – Louis Papaloizou Nov 1 '14 at 19:27
  • As limit, yes. And it is FILE - which means that if you store binary data in the filestream then this does NOT count against the limit although the data is part of the backup AND the database size. THe licensing is limited on total FILE sizes for teh data (mdf,ndf) – TomTom Nov 1 '14 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.