Question
-
This may be more of a newby type question here. I have 2 exact databases sitting on 2 different servers. It like to archive the production one once a mnth and 1)copy all data to the archive database, and b) truncate the exisiting tables on production.
What are the options available to do #1? I know you can run dts, but how do you write an insert statement between the two databases, do you have to use a linkedserver? I'd like to append to the archive database from the production one. An example would help too.
Appreciate help.
-
If you want to use TSQL only, you would need to use either a linked server or a remote server with a lot of stored procs with many parameters. I don't think you want to use identical databases though - in fact you can't if you have primary/unique keys or identity columns in the source db. I would expect to at least have an 'archival id' column on each table in the target db, and preferably a unique surrogate key as well.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
If you look at the DTS GUI interface in EM, it has options to connect to different servers. Just create two different connections. Generally speaking, you want to house the DTS in whichever database is doing the most work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply