March 10, 2004 at 10:59 am
I have 2 servers, Test & Prod. Prod has all the data.
Test's applications and DBs have already been upgraded to MyApplication v6.2. Prod's is being upgraded to v6.1.1.
After Prod's upgrade we would like to copy the data from the
Prod DBs to Test, except that with the version change/difference, I cannot easily just overwrite the Test DBs with the after upgrade prod DB backups. Tables & Fields may have been added, etc.
Is there a way to enumerate the DBs then enumerate the tables
maybe using nested T-SQL statements?
someting like:
EXEC sp_MSforeachDB 'EXEC MSforeachtable INSERT INTO Prod.Table1 SELECT * from Test.Table1'
I know my syntax is not correct, but I can't seem to find anything like this, anywhere? Can you help?
Or maybe can you suggest an alternative course of action?
Thanx in advance
jmonty
March 10, 2004 at 1:40 pm
I have never found an easy way to do this, but have had this situation many times...I usually set up a script like this for each table:
insert into test.table1 (test column list)
select matching column list from prod
from prod.table1
-- this works great if you have linked servers set up between the two. Just match up the two column lists, using default values or constants to account for any test columns that are not in prod.
-- I usually use table scripts from Enterprise Mgr or Query Analyzer to speed the script-building process. I also at times use WHERE clauses to limit test data.
-- Keep in mind that the tables in the script must be in order so that FK constraints are satisfied.
Good Luck!
Steve
March 11, 2004 at 7:06 am
There's a pretty cool tool made by Red Gate Software called SQLCompare. It's pretty handy for stuff like this since you may have the same table with the same column name in both systems, but what if the data types are different? Ouch. This tool will actually script out the T-SQL you'd need to run to make them the same.
BTW: They also have a Data Compare tool too, in case you're interested...
Cheers,
Ken
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy