ScriptOut Linked Server with Actual Password

Spread the love

Within my organization, we work in a controlled environment. That means, with each SQL Instance migration, we have strict rules to script out everything including System database user objects, operators, job categories, alerts, jobs, schedules, database mail, endpoints, logins, users and a lot more before going for the actual migration.

For moving Logins/Users, Microsoft provided revlogin script which made it easy for migration of logins without need to know about passwords. But, there is no easy approach for migration LinkedServers with the actual password. This is where dbatools cmdlet Copy-DbaLinkedServer becomes very handy. But, what about the situation where we have to script out LinkedServer beforehand.

For this reason, based on the blog post of Antti Rantasaari, and using his code as the base script, I have created a cmdlet Get-SdtLinkedServer with SQLDBATools module which accepts SqlInstance name as a parameter along with -ScriptOut switch, and gives Drop/Create statements for linked servers present on that local/remote SqlInstance.

# Install module SQLDBATools from Powershell Gallery
Install-Module SQLDBATools

# Import Module
Import-Module SQLDBATools -DisableNameChecking

# Show me Linked Server Passwords in Grid (Table)
Get-SdtMSSQLLinkPasswords -SqlInstance MyProdServer1 | ogv

# Fetch Help
Get-Help Get-SdtLinkedServer -Full

# Scriptout Linked Servers with actual password in text file, and open same
Get-SdtLinkedServer -SqlInstance MyProdServer1

PS C:\WINDOWS\system32> Get-Help Get-SdtLinkedServer -Examples

NAME
Get-SdtLinkedServer

SYNOPSIS
This function returns LinkedServers presents on SqlInstance

-------------------------- EXAMPLE 1 --------------------------

PS C:\>Get-SdtLinkedServer -SqlInstance 'testvm' | Out-GridView

Find all Linked servers with usernames and passwords present on SqlInstance 'testvm'. Display the result in Grid view.




-------------------------- EXAMPLE 2 --------------------------

PS C:\>Get-SdtLinkedServer -SqlInstance 'testvm' -ScriptOut

Get drop/create statements for all Linked servers with corrent usernames and passwords present on SqlInstance 'testvm' in a text file on MyDocuments path.




-------------------------- EXAMPLE 3 --------------------------

PS C:\>Get-SdtLinkedServer -SqlInstance 'testvm' -ScriptOut -File 'C:\LinkedServers_ScripOut_4_testvm.sql'

Get drop/create statements for all Linked servers with corrent usernames and passwords present on SqlInstance 'testvm' in sql file.

I hope this is helpful to other DBAs. Happy Coding 🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
4 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.