January 21, 2011 at 10:02 am
I've got 100+ linked servers on a SQL2k box that I want to move to a SQL2k8 installation.
I'm thinking if I add the sql2k box as a linked server and then populate DMV sys.servers by query against the old master.dbo.sysservers (some manipulation required...)
... and being a lazy DBA I don't want to have to slog through adding 100+ linked servers on the new box one at a time...
Thoughts? Anyone?
January 21, 2011 at 10:29 am
Why not just script them out in a batch via object explorer details?
Then the only manual work you'd have to do is replace the passwords...and if you're using the same password for most/all connections, do a quick/find replace and you're done.
January 21, 2011 at 10:31 am
Alternatively, script the create scripts as dynamic SQL from a cursor over the sysservers table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2011 at 1:08 pm
Derrick,
Thank you!! I didn't know it could do that!! A thing of beauty!!
January 21, 2011 at 1:10 pm
Cursor? Never!! Okay, maybe at gun point... just maybe... it better be a big gun... 😎
January 21, 2011 at 1:19 pm
For a 1-time task, the cursor is not really such a bad option.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 2:32 pm
just call me Rev. Gary from the church of No Cursors!!
January 21, 2011 at 2:38 pm
We can respect that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply