January 22, 2008 at 7:35 pm
I've got a SQL Server 2005 server (let's call it "Server_1"); and on this server there are 2 named instances (let's call them "Instance_dev" and "Instance_qa"), each holding several individual databases. There is a database (let's call it "DB_1") that exists in name in each named instance; but the databases are not synched to one another.
After making some changes to some of the data in "DB_1" on "Instance_qa", I want to copy the data to "DB_1" on "Instance_dev", replacing the data that resides there now. When I try to do this with a query that copies a table from one database to the other, I keep getting syntax errors that I can't seem to resolve (I can't seem to figure out how to identlify the named instances in my code). When I try to do it with the Import/Export tool, I get errors citing Foreign Key violations as the reason for failure.
I know this can be done; but I'm not getting anywhere. Any help wil be much appreciated.
Thanks
d
January 22, 2008 at 7:47 pm
You can run queries accross >= two databases only if those dbs are in the same instance. In order to do this you have to qualify each table in your query like db_name.db_schema(this is usually dbo).tbl_name.
Now, if you need to do this between sql instances then you would have some options:
- linked servers (read in BOL)
- import/export wizard (ssis)
- backup your sqlsrv1 database and restore it into sqlsrv2 instance under a different name; then run queries between the existing sqlsrv2 db and the newly restored one as explained at the beginning of this post; you can automate this - sql agent - if you need to do it many times per day.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply