January 12, 2015 at 6:16 pm
Hi Guys,
I have this scenario. I have to migrate just the data from 2005 to 2008R2 env.
I tried to do backup/restore, however...the client wants just the data and not any objects...i.e...SPs, Functions, Views, etc....since they add/modify changes to the objects on the new(2008R2) env.
I was thinking another approach such as snapshot replication....this was I can pick and choose what I want to replicate, if I am thinking/reading replication correctly, so please let me know what you guys think as I am not an expert in replication.
Have anyone done this before/similar scenario? Any valuable suggestions will be much appreciated. Thanks.
Regards,
SQLisAwe5oMe.
January 12, 2015 at 8:09 pm
If it's a one-time data move, the easiest is going to be to just do a restore. You can drop all the objects on the other side after the restore. You could set up mirroring or replication, sure. But that's a lot of work for a single data move. Exporting the data to flat files and importing it is probably less work and will get you to the same place. You can export the data structures first and migrate those in a SQL script so they're intact.
But I'd just go with the restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2015 at 9:15 pm
Grant Fritchey (1/12/2015)
If it's a one-time data move, the easiest is going to be to just do a restore. You can drop all the objects on the other side after the restore. You could set up mirroring or replication, sure. But that's a lot of work for a single data move. Exporting the data to flat files and importing it is probably less work and will get you to the same place. You can export the data structures first and migrate those in a SQL script so they're intact.But I'd just go with the restore.
Hi Grant, thank for the reply...but I am not really getting everything you are saying.
The restore option failed because in the new env, they have additional or basically all the objects are modified excluding tables. So, by doing the simple backup and restore will overwrite the objects on the destination side which the client doesn't want.
Yes, this is for a 1 time go live scenario and they just want to carry over just the data from old(current prd) to new prd env.
I hope I am clear here with the requirement. I was thinking, by using replication, I could just replicate the data by selecting all the tables to replicate and exclude any other objects. I have not done it before, so I am not sure if it will be successful.
Regards,
SQLisAwe5oMe.
January 13, 2015 at 1:43 am
SQLisAwE5OmE (1/12/2015)
Grant Fritchey (1/12/2015)
If it's a one-time data move, the easiest is going to be to just do a restore. You can drop all the objects on the other side after the restore. You could set up mirroring or replication, sure. But that's a lot of work for a single data move. Exporting the data to flat files and importing it is probably less work and will get you to the same place. You can export the data structures first and migrate those in a SQL script so they're intact.But I'd just go with the restore.
Hi Grant, thank for the reply...but I am not really getting everything you are saying.
The restore option failed because in the new env, they have additional or basically all the objects are modified excluding tables. So, by doing the simple backup and restore will overwrite the objects on the destination side which the client doesn't want.
Yes, this is for a 1 time go live scenario and they just want to carry over just the data from old(current prd) to new prd env.
I hope I am clear here with the requirement. I was thinking, by using replication, I could just replicate the data by selecting all the tables to replicate and exclude any other objects. I have not done it before, so I am not sure if it will be successful.
Restore to a different database and then copy the data using INSERT/SELECT.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2015 at 1:44 am
Script all their modified procs, views, functions out. Backup, restore with overwrite. Run script with all objects (take a backup of their DB first, just in case)
Likely to be faster than copying all the tables across one by 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
January 13, 2015 at 4:10 am
Maybe I misunderstood the question then. You're saying that they already have built an empty database, but this time it's got new structures, different table layout, etc., in addition to new procedures, etc.? Is that right?
In this case replication is an even more poor choice because you would be forced to customize the replication process. Replication, except through customization, traditionally goes from one database to a like databases. Having to adjust the structures and layout of the data in flight is possible, but is even more work than simply setting up replication. I strongly recommend against that as the approach.
Instead, I see three possibilities. One, you build SQL Server Integration Services (SSIS, the built-in SQL Server Extract Transform & Load, ETL, tool) to move the data over. Since it's only a one-time load, this could be overkill. Second, export all the data out to flat files as I said before and then import them into the new structures. Or, take Jeff's advice and restore a copy of your database locally and then use SELECT statements to pull data across from your structure to the new structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2015 at 5:38 am
I agree with Jeff.
After restore, I would use the wizard to copy the data from table to table (depending on number of tables).
January 13, 2015 at 6:44 am
The other possibility (correct me if I'm wrong, everyone) is to use the DTSX Import Wizard from the 2008R2 server to grab the data from the 2005 server. It won't work the other way around, but as long as the two servers can communicate, it should work from 2008R2 to 2005.
But I wouldn't say this is your best option. Just another option. (EDIT): And you have to be really careful with the views. They tend to show up as tables in the Wizard.
January 13, 2015 at 6:53 am
GilaMonster (1/13/2015)
Script all their modified procs, views, functions out. Backup, restore with overwrite. Run script with all objects (take a backup of their DB first, just in case)Likely to be faster than copying all the tables across one by one.
Gail, I tried this approach, but only difference is that they don't know the list of objects modified and told me it's basically all of the objects. Therefore, I script out all objects.
After the restore I executed the script and a lot of failure because it says the objects already exists....
I would think it's because we are trying to create the same objects again. Alter statement instead of create might work but we are talking about 100s of objects and I have 24 dbs to migrate.
So, I'm running out of options and not sure what is the cleanest way to do this.
Also, I tried the import/export, some failed due to dependencies/constraints, etc.
Regards,
SQLisAwe5oMe.
January 13, 2015 at 6:58 am
SQLisAwE5OmE (1/13/2015)
GilaMonster (1/13/2015)
Script all their modified procs, views, functions out. Backup, restore with overwrite. Run script with all objects (take a backup of their DB first, just in case)Likely to be faster than copying all the tables across one by one.
Gail, I tried this approach, but only difference is that they don't know the list of objects modified and told me it's basically all of the objects. Therefore, I script out all objects.
After the restore I executed the script and a lot of failure because it says the objects already exists....
So you didn't script them out as DROP and CREATE, but only CREATE?
Rescript them as both DROP & CREATE. It will resolve that issue.
January 13, 2015 at 7:04 am
You could try looking at third party tools then. Redgate SQL Compare for the structures and SQL Data Compare for the data. You'll have to do manual mapping on the objects to get columns connected to each other when they're different between the structures. I think this will do what you're attempting, but I'm still a little unclear on what the end-game looks like. (Disclaimer: I work for Redgate)
This really sounds like a development process gone horribly wrong.
I also think you may be trying to do everything at once. If the issue is to move data, then concentrate only on data. Don't try to move the tables themselves, but get a process that moves the data. Will there be referential issues? Yep. You'll have to work that out through a series of tests (although the Redgate tools handle that for you, largely).
I'm still leaning towards scripting out the data and writing import scripts or using Jeff's approach of a separate database and then queries to migrate the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2015 at 7:35 am
Brandie Tarvin (1/13/2015)
SQLisAwE5OmE (1/13/2015)
GilaMonster (1/13/2015)
Script all their modified procs, views, functions out. Backup, restore with overwrite. Run script with all objects (take a backup of their DB first, just in case)Likely to be faster than copying all the tables across one by one.
Gail, I tried this approach, but only difference is that they don't know the list of objects modified and told me it's basically all of the objects. Therefore, I script out all objects.
After the restore I executed the script and a lot of failure because it says the objects already exists....
So you didn't script them out as DROP and CREATE, but only CREATE?
Rescript them as both DROP & CREATE. It will resolve that issue.
Yes, this might work.
I have to try and see....and I have to exclue tables and select all other objects.
Regards,
SQLisAwe5oMe.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply