March 31, 2015 at 4:51 pm
I need to run some queries against a client's SQL Server database that is 9,333,558 KB in size. I am trying to restore the .bak file to a fresh instance of SQL Server Express 2008 R2. I am getting error 1827 "resulting cumulative database size would exceed license limit of 10240 MB per database".
I run these queries once per year. I do not alter the data, only query it. When I am done, I delete the database. The .bak file will never be restored anywhere else.
The last time that I restored it to my SQL Server Express 2008 R2, the size was 8,839,830 KB and everything went smoothly.
Are there any tricks to reducing the size of the .bak file that I have, or is there a way to restore just a selected set of tables from the .bak file instead of the entire set of 120 tables?
If not, are there any other options available to me other than purchasing a full license to SQL Server for the once a year that I need to do this?
Any advice would be greatly appreciated!
Thanks,
Suf
March 31, 2015 at 8:11 pm
It's less a question of reducing the size of your backup file and more a question of the size of the original database / data file sizes from the source database. Since the backup will not restore, it appears that it contains a database that would create data files larger than the 10gb limit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2015 at 6:04 am
the cost of a developer edition license is in the range of fifty or so dollars on Amazon, and well worth the investment.
that will get you over your limitations imposed by express, and provide you a better developer platform to boot to work on these yearly one off instances.
also i believe having a test instance is covered under your standard/enterprise license anyway,so you can test and then promote to production, so there's the option of restoring it to your test environment instead of locally.
Lowell
April 1, 2015 at 6:57 am
I understand that the fully restored database will be larger than the 10 GB limit of SQL Server Express.
I am asking if there is a way to restore a read-only portion of the full backup that will fit inside the 10 GB limit so that I can run my queries. From there, I will make an ODBC connection and use Access query the data.
The database has 5 files. I have tried to edit the restore script to eliminate 2 of the 5 files in the backup, but I still get the size limit failure. Is there a way to build a new database using only 1 or 2 of the 5 mdf files contained in the backup file?
Thanks,
Suf
April 1, 2015 at 7:07 am
sufstuff01 (4/1/2015)
I am asking if there is a way to restore a read-only portion of the full backup that will fit inside the 10 GB limit so that I can run my queries. From there, I will make an ODBC connection and use Access query the data.
No.
The only time you can do partial restores is when the DB consists of multiple filegroups. Not multiple files, but multiple file groups.
With multiple files in one file group, the data is spread across all 5 files, so can't be used in part (and the restore isn't simply done with removing some files, you have to state the file groups you want to restore)
And I don't know whether that will get around Express limits anyway, even if there are multiple filegroups. It probably checks the database size in the metadata of 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
April 1, 2015 at 7:28 pm
Thanks for the info. I didn't realize that developer edition was $50
suf
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply