Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-11-15 : 10:59:55
|
| Hi guys,We have 2 databases: DataBaseSource and DatabaseDestination. We need to truncate all the data in DatabaseDestination and put all the data from DataBaseSource into DatabaseDestination.What is the best way to do that, we have a lot of data?And what is the best way if we also need to keep a trace of what happened in case we wanna go back and see what happened.Also , pls, if we use DTS, is it possible that if someone wants to see what the DTS does, is it possible to read the DTS? I mean if I give a dts to sompeone, a new DBA guy in 2 years for example, how can he know what a certain DTS does? I mean does SQL 2005 put the DTS packaege scripts somewhere or is there a friendly way to know what a DTS dsoes exactly?Also the trace to see if something went bad is important for us?Sory if i didn t express myself well enough, and thanks a lot for your help.Rachid. |
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-15 : 11:13:05
|
| Hi there are a number of wasy to do this. And no way is right.First off to keep a history of what you did I would take a back up, so that if you did not to put the old data back in the destination database its totaly posible.Then to move the data over, you can use a number of dirrent ways.DTS, You could create a dts, that would drop all data, and then restore it, DTS packages can be saved on the server or as a file and can be re-read at at any time, and a dba sould be able to easly see what you did.Replication would be another way, using a snapshot replication would do the same thing and would be good if you plan to do this on a regulaer basis.Also there are tools that would do this like red gates data compare.Hope this helps.Dave |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-11-15 : 11:13:59
|
| I would bcp the data out from the source, truncate the destination then bcp the data in.The only issues are connectivity and permissions and is probably 3 lines of t-sql.Also means that you don't need to access the source and destination at the same time and can rerun the import from the file if it fails.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ratheeshknair
Posting Yak Master
129 Posts |
Posted - 2007-11-15 : 11:18:54
|
| If u want to truncate all the tables in one database and put those datas in another the best is 1)Backup both databases.(This will help u if something goes wrong)2)Restore DatabaseDestination with the dump of DataBaseSource.What is the need of truncating the tables in DatabaseDestination???If so use truncate table command to truncate each table.RKNAIR |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-11-15 : 13:28:29
|
| Thanks a lot , I appreciate your kindness :) |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-11-15 : 13:32:13
|
| one more question please:You say DTS can be re-read to see what it does. How can we re-read the DTS.Also, can we trace what a DTS did after the execution, whether every thing went well, or something did not go well. how pls?Thanks a lot. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-15 : 22:54:02
|
| You can trace it in profiler. |
|
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-16 : 04:19:35
|
| DT can be opened as a file in dts viewer or saved on the server or as a file. If a dts is well writen you should incuded error loggin at each stage. But the DTS when run will show you how it got on. |
|
|
|
|
|
|
|
|
tracing , history and analyzing DTS package