ScriptOut Linked Server with Actual Password
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-------------------------- 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 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Hello Ajay, Thanks for the script. But someone the script is not working for me. its not returning the data.
Hi Ayesha,
Kindly raise an issue on my github project by visiting below url:-
https://github.com/imajaydwivedi/SQLDBATools/issues
Please be as much descriptive as possible. Make sure to put all error details.
Regards,
Ajay Dwivedi
Hi Ajay, when we ran the query beneath the Get-MSSQLLinkPasswords it clearly said the master.sys.syslnklgns object is invalid, is it dependant on versions ?
Hi Nilanjan,
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/system-base-tables?view=sql-server-ver15
Above page suggests the object still exists in SQL 2019 in master database.
Regards,
Ajay