April 24, 2015 at 6:55 am
As part of a DR enhancement, I'd like to have a SQL Agent job that runs daily and writes out scripts to recreate logins, linked servers and sql agent jobs to text file. Logins and Jobs is pretty easy- there are existing scripts out there that I can just call from an agent job. I can't seem to find anything similar for linked servers. Basically, all I want to do is automate the "generate scripts" function for linked servers... anyone know if this code already exists somewhere?
April 24, 2015 at 7:56 am
you can right click a linked server and choose 'script linked server as' > 'create to' > 'new query'
do this for each linked server. That what you're after?
April 24, 2015 at 8:33 am
That's what I'm after, but I want to automate that... Instead of right clicking, I want to genereate the scripts via SQL Agent job every morning. Essentially, I'm looking for a script to create the scripts.
April 24, 2015 at 8:39 am
do you have loads of linked servers? to do them all at the same time, click and highlight 'linked servers' under 'server objects', then click 'view'>'object explorer details', you will then be able to select all of the linked servers, right click and create all to a query window.
Then copy this syntax into a sql job.
April 24, 2015 at 8:47 am
You dont understand- I dont want to create the linked servers every day. I want to generate the scripts every day... so that if new linked servers are added or changed, each day the script will be up to date with those changes. I know how to manually script out linked servers. I'm talking about generating the scripts automatically. For example, when you click "generate script" some code takes the details from system tables and produces a script of them. That's what I want to do automatically every day.
April 24, 2015 at 8:49 am
yeah sorry, replied then read your update properly!
April 24, 2015 at 8:57 am
Here's a tool for this job: https://scriptsqlconfig.codeplex.com/
BTW, it can script more than just linked servers.
-- Gianluca Sartori
April 24, 2015 at 9:34 am
that tool is cool, pretty much what I'm looking for- thank you!
March 29, 2018 at 9:59 am
This is fast sql script that will make a sql script (create linked server command) for linked servers (made by me), but without password:
select
'
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'''+a.name+''', @srvproduct=N'''+a.product+'''
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+a.name+''',@useself=N'+case when uses_self_credential = 0 then '''False''' else '''True''' end +',@locallogin=NULL,@rmtuser=N'''+b.remote_name+''',@rmtpassword=''########''
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''collation compatible'', @optvalue=N'+case when is_collation_compatible = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''data access'', @optvalue=N'+ case when is_data_access_enabled = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''dist'', @optvalue=N'+ case when is_distributor = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''pub'', @optvalue=N'+ case when is_publisher = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''rpc'', @optvalue=N'+ case when is_remote_login_enabled = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''rpc out'', @optvalue=N'+ case when is_rpc_out_enabled = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''sub'', @optvalue=N'+ case when is_subscriber = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''connect timeout'', @optvalue=N'''+convert(varchar,connect_timeout)+'''
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''collation name'', @optvalue='+case when collation_name is null then 'null' else collation_name end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''lazy schema validation'', @optvalue=N'+ case when lazy_schema_validation = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''query timeout'', @optvalue=N'''+convert(varchar,query_timeout)+'''
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''use remote collation'', @optvalue=N'+ case when uses_remote_collation = 0 then '''False''' else '''True''' end +'
GO
EXEC master.dbo.sp_serveroption @server=N'''+a.name+''', @optname=N''remote proc transaction promotion'', @optvalue=N'+ case when is_remote_proc_transaction_promotion_enabled = 0 then '''False''' else '''True''' end +'
GO
' as create_linked_server_command,
a.name
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
where a.server_id > 0
and remote_name is not null
---------------------------------------------------------
Database Comedian as my Friends says 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply