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.

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.

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.

This error comes when you are trying execute distributed transaction via linked server. Personally I don’t prefer linked server and would always go for other alternatives like SSIS package or something else but one of my friend had a situation where he must had to setup linked server to make distributed transaction. Security wasn’t concern for him as his network was just an intranet and under hardware as well as software firewall so I have helped him to setup distributed transaction for linked server so that he can fix the error:

Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “MARSLINK” was unable to begin a distributed transaction

For example we have two server named “SATURN” and “MARS” accordingly. We have set link server of MARS in SATURN so that I can access database of MARS from SATURN. I wanted to execute one SP from one of the database of MARS from SATURN and it was working fine after setting up linked server but when I tried to take output of this SP into one temp table, it started popping up the error given above.

Let us try to resolve this error.

First I will connect to first server which is SATURN in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab

134

Select “Network DTC Access” checkbox and also select “Allow Outbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Once SATURN is configured, let us do the same in MARS with minor change:

Now, I will connect to second server which is MARS in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab .

136

Select “Network DTC Access” checkbox and also select “Allow Inbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Now, you will be able to execute distributed transaction via linked server.

I have some more article on the subject of Linked Server, have a look at it, if you are interested on this subject.

Create Linked server with Excel 2007 worksheet in SQL Server 2005 (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)

Linked Server in SQL Server 2005 from ACCESS 2007 (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.