September 27, 2013 at 2:17 am
I have a DEV database i.e DEVDB
I also have a TEST database i.e TESTDB
both are SQL SERVER 2008
I want to import data from TESTDB to DEVDB.
Is it possible through SQL Server Management Studio Wizard ? Is it faster than other approach ?
I see a Import/Export on GUI of SQL Server Management Studio Wizard. Which datasource to use to copy data ?
September 27, 2013 at 2:42 am
Which datasource to use to copy data ?
SQL Native Client
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
September 27, 2013 at 3:13 am
spectra (9/27/2013)
Is it possible through SQL Server Management Studio Wizard ? Is it faster than other approach ?
Which other approach?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 27, 2013 at 3:28 am
You might want to take a look at SQL Server Integration Services, SSIS. That's the standard Extract, Transform, Load (ETL) tool for SQL Server.
"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
September 27, 2013 at 3:40 am
does it overwrite data in destination DB ?
I want to refresh destination DB in fact.
September 27, 2013 at 3:49 am
spectra (9/27/2013)
does it overwrite data in destination DB ?I want to refresh destination DB in fact.
That's up to you, how you configure it.
If you just want to overwrite the data, why not go a simpler route. Use a backup and a restore. That's guaranteed to move all the data as well as the structures and with minimal coding required.
"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
September 27, 2013 at 8:39 am
Is not backup and restore mess up permission ?
I am scared about it....I don't want to set up permission again.
All I need is fresh data imported from TEST DB
also I have few extra tables in destination DB . I want to keep them as well.
So what do I do ? can it be done using GUI ?
September 27, 2013 at 9:07 am
As to permissions, it's pretty easy to script them out and reapply them as part of a restore.
But, if you have differences in structure, there is no way to run a restore and keep them in place. So, your choices are to script those out too, or build a data load process.
The GUI is unlikely to do a complete job for you considering the different requirements you have. So, you're going to have to manually set it up within SSIS. It's not terribly difficult, but if you've never done it before, it's going to be a lot of work.
"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
September 27, 2013 at 9:45 am
This is something I do all the time. There are several methods you can choose from. The first is a real simple method that you discovered. The Import/Export feature actually creates an SSIS package to perform the data movement. What I do to move just data, I select all tables then when all of them are highlighted (in the Import wizard) you Edit Mappings to "DELETE Rows in existing destination tables" & "Enable identity insert". If there are no errors you can "Run immediately" and just your data moves to the destination database.
Now I have a data compare tool from RedGate called SQL Data Compare. This does much the same thing and works very well. It just simply compares and moves data from 1 database to another.
I move data all the time because 2 database structures may be different, especially when the developers are working in the DEV environment.
Now if you know the data structures are the same and don't worry about overwriting the entire database then your backup/restore is probably the easiest.
If the databases reside on the same box then you don't have to worry about creating orphan users. If they reside on different boxes then you have to insure your DB users don't become orphans. There is a script you can dig up on the net that resolves orphans.
So you have some choices, or "there is more than 1 way to skin that cat". Let us know what you choose.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 28, 2013 at 3:29 am
Kurt W. Zimmerman (9/27/2013)
This is something I do all the time. There are several methods you can choose from. The first is a real simple method that you discovered. The Import/Export feature actually creates an SSIS package to perform the data movement. What I do to move just data, I select all tables then when all of them are highlighted (in the Import wizard) you Edit Mappings to "DELETE Rows in existing destination tables" & "Enable identity insert". If there are no errors you can "Run immediately" and just your data moves to the destination database.
this failed.
I got error "Can not truncate table dbo.Account Transfer because it is referenced by a foreign key constarint" ....
September 28, 2013 at 3:54 am
Kurt W. Zimmerman (9/27/2013)
Now I have a data compare tool from RedGate called SQL Data Compare. This does much the same thing and works very well. It just simply compares and moves data from 1 database to another.
This does not look like FREE version....Is there any free alternative ?
September 28, 2013 at 8:33 pm
Why don't you just script out the tables you need and apply at destination?
Regards,
SQLisAwe5oMe.
September 29, 2013 at 1:26 pm
spectra (9/28/2013)
Kurt W. Zimmerman (9/27/2013)
This is something I do all the time. There are several methods you can choose from. The first is a real simple method that you discovered. The Import/Export feature actually creates an SSIS package to perform the data movement. What I do to move just data, I select all tables then when all of them are highlighted (in the Import wizard) you Edit Mappings to "DELETE Rows in existing destination tables" & "Enable identity insert". If there are no errors you can "Run immediately" and just your data moves to the destination database.this failed.
I got error "Can not truncate table dbo.Account Transfer because it is referenced by a foreign key constarint" ....
That's because TRUNCATE TABLE doesn't work if there are foreign keys.
The GUI alone won't be enough. Go through the GUI, but instead of executing it at the end, save the SSIS package.
Open it up with Visual Studio and edit it until you implemented all of your requirements.
Better search some tutorials about SSIS on the net.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 30, 2013 at 7:59 am
If you haven't done this kind of thing before you might want to take a backup of the destination database before you do your import... just in case.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply