Backup Linked Server script in SQL Server by SSIS

Backup Linked Server script in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database only, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for all Linkedserver in SQL Server instance by SSIS.

As long as possible, I, personally, try to avoid linked server but even in some scenario DBA used to use linked server because of any reason (don’t want to start debate of whether to use linked server or not). while making a plan for failover or for creating another server/instance of one of the production server, DBA has to keep Linked Server in mind otherwise some package, view, SP etc. can break in new server/instance if it is uses linked server.

Let us now create one SSIS package to generate the script of all Linked Server from SQL Server. I used to keep monthly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of linked server every month.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]
SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
‘Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ”A” and name = ”ALL SETTABLE OPTIONS”

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 open d

fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname

SET NOCOUNT ON

while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values(”——————–” + @Server + ”——————–”)
If @status in (64,65)
Begin
Insert Into @LinkedServer values( ”sp_addserver”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(” GO”)
If @status = 64
Begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values( ” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values( ” @optname = ””rpc””,”)
Insert Into @LinkedServer values( ” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
End
exec (”declare r cursor for select l.name, r.remoteusername from sysremotelogins r join sysservers s on r.remoteserverid = s.srvid join syslogins l
on r.sid = l.sid where s.srvname = ”””+ @server + ””””) open r fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_addremotelogin”)
Insert Into @LinkedServer values(” @remoteserver = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @loginame = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @remotename = ”””+ @rmtlogin + ””””)
Insert Into @LinkedServer values(” GO”)

fetch next from r into @loclogin, @rmtlogin end close r deallocate r
End
Else
Begin
If exists (select * from tempdb..sysobjects where name like ”#tmpsrvoption%”)
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption ( srvoption varchar(30) )

insert #tmpsrvoption
select v.name from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid and (v.number & s.srvstatus)=v.number and (v.number & isnull(@allsetopt,4063)) <> 0 and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = ”A” Insert Into @LinkedServer values( ”sp_addlinkedserver”)

Insert Into @LinkedServer values(” @server = ”””+ @server + ””””)
Insert Into @LinkedServer values(”, @srvproduct = ””” + @srvproduct + ””””)
If @srvproduct <> ”SQL Server”
Begin
Insert Into @LinkedServer values( ”, @provider = ””” + isnull(@provider,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @datasrc = ””” + isnull(@datasrc,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @location = ””” + isnull(@location,”NULL”) + ””””)
Insert Into @LinkedServer values(”, @provstr = ””” + isnull(@provstr,”NULL”) + ”””” )
Insert Into @LinkedServer values(”, @catalog = ””” + isnull(@catalog,”NULL”) + ””””)
End

Insert Into @LinkedServer values( ” GO”)
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””rpc out””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)
Insert Into @LinkedServer values(”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ””data access””,”)
Insert Into @LinkedServer values(” @optvalue = ””false”””)
Insert Into @LinkedServer values(” GO”)

declare s cursor for SELECT srvoption from #tmpsrvoption open s
fetch next from s into @srvoption while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ”sp_serveroption”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @optname = ”””+ @srvoption + ”””,”)
Insert Into @LinkedServer values(” @optvalue = ””true”””)
Insert Into @LinkedServer values(” GO”)

fetch next from s into @srvoption
End

close s
deallocate s

If exists (select * from tempdb..sysobjects where name like ”#tmplink%”)
Begin
drop table #tmplink
End

create table #tmplink ( rmtserver sysname, loclogin sysname null, selfstatus smallint, rmtlogin sysname null )

insert #tmplink exec (”sp_helplinkedsrvlogin ”””+ @server + ””””)

declare ll cursor for select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin open ll fetch next from ll

into @loclogin, @selfstatus, @rmtlogin while (@@FETCH_STATUS<>-1)

begin If (@selfstatus = 1 and @loclogin is null)

Begin

Insert Into @LinkedServer values( ”sp_addlinkedsrvlogin”)

Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)

Insert Into @LinkedServer values(” @useself = ””true”””)

Insert Into @LinkedServer values(” GO”)

End

Else If (@selfstatus = 1 and @loclogin is not null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””true””,”)
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0 and @rmtlogin is null)
Begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)
Insert Into @LinkedServer values(” @locallogin = NULL,”)
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
Insert Into @LinkedServer values(” GO”)
End Else If (@selfstatus = 0)
Begin
exec (”declare password cursor for select l.password from master..sysservers s join master..syslogins l on s.srvid = l.sid
where s.srvname = ”””+ @server + ””” and l.name = ”””+ @rmtlogin + ””””)

open password fetch next from password into @rmtpass while @@fetch_status = 0
begin
Insert Into @LinkedServer values(”sp_addlinkedsrvlogin”)
Insert Into @LinkedServer values(” @rmtsrvname = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @useself = ””false””,”)

If (@loclogin is null)
Begin
Insert Into @LinkedServer values(” @locallogin = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @locallogin = ”””+ @loclogin + ”””,”)
End

If (@rmtlogin is null)
Begin
Insert Into @LinkedServer values(” @rmtuser = NULL,”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtuser = ”””+ @rmtlogin + ”””,”)
End

If (@rmtpass is null)
Begin
Insert Into @LinkedServer values(” @rmtpassword = NULL”)
End
Else
Begin
Insert Into @LinkedServer values(” @rmtpassword = ”””+ @rmtpass + ””””)
End

Insert Into @LinkedServer values(” GO”)

fetch next from password into @rmtpass
end
close password

deallocate password
End

fetch next from ll into @loclogin, @selfstatus, @rmtlogin End close ll deallocate ll
End

If @netname <> @server
Begin
Insert Into @LinkedServer values( ”sp_setnetname”)
Insert Into @LinkedServer values(” @server = ”””+ @server + ”””,”)
Insert Into @LinkedServer values(” @network_name = ”””+ @netname + ””””)
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname
End close d deallocate d Select [LinkedServer] From @LinkedServer’

Exec (@SQL)
[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2PropertyofDataflow

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “LinkedServer.sql” which I already had. You can keep blank SQL file in your destination.

33FileDestination

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “LinkedServer” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

FinalExecutionBackupLinkedServerScriptSSISPackage

We have many different server/instance and need script for all Linked Server from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

Note: If you have set password for your linked server, this script won’t decrypt password and give it to you. You have to manually change it in the script this SSIS package will create.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

I have written few more Linked Server related articles in past, have a look if you are interested.

  • Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)
  • Linked Server in SQL Server 2005 from ACCESS 2007 (click Here)
  • Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)
  • Linked Server is not configured for data access Error: 7411 in SQL Server 2005(Click Here)
  • Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkServerName” was unable to begin a distributed transaction. (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Backup Linked Server script in SQL Server by SSIS”

Comments are closed.