October 16, 2013 at 8:31 am
Hi,
My production database has 2008 R2 enterprise edition and testing database server is 2008 R2 standard edition. Also my production database has some partition tables. I am trying to take the back up of my production database and restore it on the test environment standard version of database.
Because of the partition table i am unable to restore it on the standard edition of sql database server.
few constraints stops me doing that.
i cannot drop the partition as the partitioned table's primary key is referenced in many tables. So is there any way to take the backup without partition and restore it on the another server?
October 16, 2013 at 8:37 am
NO i think you have to restore the data into non partitioned table then move the data to other server.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 16, 2013 at 8:48 am
Since you are using enterprise features in production, you need to have enterprise features available in your test/development environments.
Installing Enterprise Edition in a test/development environment is expensive, however there is an alternative. You can purchase a copy of the Developer Edition of SQL Server 2008 R2 (one copy per developer and tester) for about 50 - 75 per person and install it on your development/test servers. The Developer Edition has all the same features as the Enterprise Edition. The licensing is such that it can only be used in development/test environments.
October 16, 2013 at 8:57 am
Thank you Bhuvesh,
is it possible to take only schema backup without partition?
October 16, 2013 at 9:24 am
I think if I were you I would create a new database on your Enterprise server in a form that can be restored on your Standard version.
With that said there is missing information here. 1) How big is the database that is going to your Standard environment? 2) Do you have enough room to stage a copy of your production database on your Enterprise environment?
Another though, you can script the Enterprise database out, alter it so you can use the script on your Standard environment. Then simply import the production data to the Standard box using an SSIS data import process.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 16, 2013 at 9:52 am
If I may suggest - change the dev environment to Developer edition. Firstly it's cheaper than standard, second it allows enterprise features
Otherwise you'd have to drop the partitioning prior to taking a backup in order to restore to standard edition (or restore to another enterprise edition, drop partitioning then take a new backup, etc)
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
October 16, 2013 at 12:27 pm
Hi Gila,
no where i can drop the partitioning in production as it is being used now. any other hope please ?
October 16, 2013 at 12:44 pm
born2achieve (10/16/2013)
Hi Gila,no where i can drop the partitioning in production as it is being used now. any other hope please ?
As both Gail and I suggested, look at installing the Developers Edition of SQL Server in your test and development environment. It is actually cheaper than using Standard Edition and provides you with the enterprise features you are using in production.
October 16, 2013 at 12:47 pm
Or you can install an evaluation edition on another server, restore db, remove partitioning and...
But still better solution is to use developer license.
October 16, 2013 at 7:23 pm
Thank you Lynn. I will try that.
October 16, 2013 at 9:38 pm
born2achieve (10/16/2013)
no where i can drop the partitioning in production as it is being used now. any other hope please ?
As I said, you can:
- Install developer edition instead of standard
- Drop the partitioning before you take the backup
- Restore the backup to another enterprise edition, drop the partitioning there, back up again, restore to standard edition
Pick one.
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
October 17, 2013 at 5:26 am
H Gila,
Thanks for your answer. I would like to for step3. also i tried step 3 yesterday. the problem is i am unable to remove the partition and file group because it says "it is being used. cannot be removed". Because the partition created on primary key(clustered index). because of this i cannot drop the constraint also as it is referenced in 8 tables as foreign key.
then i tried to remove all the foreign key constraint belongs to the primary key table(partitioned) and primary key constraint .now i am able to remove the partitioning. but i am unable to remove the file groups.it keep on says "it's being used"
any suggestions please
October 17, 2013 at 6:17 am
born2achieve (10/17/2013)
but i am unable to remove the file groups.it keep on says "it's being used"
YOu cant do that if the data (table/index ) sitting on that filegroup.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 17, 2013 at 7:27 am
Hello bhuvnesh,
I have removed the indexes on the table level. The result of below query returns no row.
select distinct t.name
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.partition_number <> 1
Which means that i have removed the partition completely whereas why the file group cannot be removed. any suggestions.....
October 17, 2013 at 7:33 am
In addition to removing the partitions from the table(s) you will also need to drop the partition schemes and partition functions. As long as those exist you will still not be able to restore a backup from an Enterprise Edition server to a Standard Edition server.
We ran into that issue out here where I am working. Someone had created these on an Enterprise System and even though none of the tables had been partitioned, their presence prevented the restoration of the database to a Standard Edition server.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply