August 30, 2015 at 3:12 pm
Hi All,
We are migrating SQL 2008 instances to SQL 2014. Looking for help regarding Linked Servers.
Is there a way to script out all linked servers along with passwords?
Appreciate if anyone can help.
Thank you.
August 30, 2015 at 6:45 pm
I have never seen a method that allowed for you to pull that password as you can with logins. However, I did come across this post where someone figured it out and provided a PowerShell script[/url]. So note: try at your own risk.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 31, 2015 at 12:01 pm
Thank you. Again, just checking if there is a script which can script out all the Linked servers for a SQL instance at 1 go?
August 31, 2015 at 12:08 pm
vsamantha35 (8/31/2015)
Thank you. Again, just checking if there is a script which can script out all the Linked servers for a SQL instance at 1 go?
Yes that process can be scripted out. Is it a script that someone has published for anyone's use, no.
It would be fairly easy to do with PowerShell and SMO. You would simply connect to your instance and look for the SMO object that contains the Linked Server objects. There should be a method in that object of Script() that you just that output to a file.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 21, 2018 at 4:58 am
Yes there is:
in Management Studio (object explorer) click on the tab "LinkedServers" Press F7 all the Linked Servers should now be on the right hand side, Press CTRL and highlight them all right click Script as, and Bosh there is your script 🙂 NB: naturally the permissions if any will need to be addressed, but really you should be testing these connections on your other/new SQL server
agreed not the most elegant solution but it worked for me 🙂
March 21, 2018 at 4:26 pm
The ability to script out all linked servers can be done now using dbatools PowerShell module.
Copy-DbaLinkedServer will let you migrate the linked server to another instance, and will attempt to decrypt the password.
Get-DbaLinkedServer can be used to pull all the linked servers on a given instance, and then piped to Export-DbaScript. This used the same scripting method as SSMS so it will not pull the password out either.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply