Scripting Linked Servers
-
Hi Gurus,
I need to script out all my linked servers as you would go to create them manually using TSQL.
There is a script post from Chad Miller which I've had a go at using, but unfortunately its for SQL Server 7 and 2000.
It works up to a point, but bombs out and obviously won't decrypt the passwords, is there a way of getting all the info EXCEPT the passwords?
Could anyone help me with an updated version for 2005?
Many thanks in advance.
Chris
-
If you browse in your ssms object explorer down to Objects' and the click 'Linked Servers', the linked servers list will appera in the right hand windows (Object Explorer Details).
Select all (click on first then holding down SHIFT click on last), right click on the highlighted list and select 'Script Linked Servers As' --> 'Create To' --> 'New Query Editor Window'.
Your linked servers should then be scripted. As far as passwords are concerned, these will be hashed (encrypted), this wont be a problem as long as the server your running this on has the same user (username and sid) and password.
Good luck
Adam Zacks-------------------------------------------Be Nice, Or Leave
-
Hi Adam thanks for responding.
I know I can do that manually, but I've got 4 reporting servers to keep an eye on and no DBA with the time to help.
What I really need to do is automate the scripted creation t-sql for all my linked servers out to a file, regularly.
I know we should have backups but life here is more complicated than it should be
Thanks though.
Chris
-
Chris Helm-389957 (2/8/2010)
Hi Adam thanks for responding.I know I can do that manually, but I've got 4 reporting servers to keep an eye on and no DBA with the time to help.
What I really need to do is automate the scripted creation t-sql for all my linked servers out to a file, regularly.
I know we should have backups but life here is more complicated than it should be
Thanks though.
Chris
Ok, then based on the format of the output of my previous post, run the below and build some dsql to create the addlinked_server statements for you.
select * from sys.servers where is_linked=1
For example i'd right the results of above query to a table and then run-around inside the table picking out the vaules and using them to build your create statement. This could then be output to a flat file, dated and emailed.
I would get the logic for this worked out in ssms and the translate to ssis. Sorry i dont have time to do it for you but im sure you'll have fun.
Adam Zacks-------------------------------------------Be Nice, Or Leave
-
:laugh:
Thanks Adam,
I'm gonna have a play around as you suggest, I'd already started actually based on Chad's original.
Cheers...
Chris.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply