October 31, 2007 at 6:41 am
Hi,
We intend to reformat and reinstall the O/S and SQL server 2000 SP4 in one of our database server.
Is there a way to backup the linked server? So when the infra team completed with their installation, I could just restore the linked server without a need to recreate manually the linked server using sp_addlinkedserver.
Thanks
October 31, 2007 at 8:22 pm
Here is the script which I got from here only.
--Script to script out all linked/remote servers
--Works on 7.0 and 2000 servers
--remote password decrypt only works on 7.0
declare
@status smallint, -- server status
@server sysname, -- server name
@srvid smallint, -- server id
@srvproduct nvarchar(128), -- product name (dflt to ss)
@allsetopt int, --sum of all settable options
@provider nvarchar(128), -- oledb provider name
@datasrc nvarchar(4000), -- oledb datasource property
@location nvarchar(4000), -- oledb location property
@provstr nvarchar(4000), -- oledb provider-string property
@catalog sysname, -- oledb catalog property
@netname varchar(30), -- Server net name
@srvoption varchar(30), -- server options
@loclogin varchar(30), -- Local user
@rmtlogin varchar(30), -- Remote user
@selfstatus smallint, -- linked server login status
@rmtpass varbinary(256), -- linked server login password
@pwdtext nvarchar(128), -- linked server decrypted password
@i int, -- linked server pswd decrypt var
@lsb tinyint, -- linked server pswd decrypt var
@msb tinyint, -- linked server pswd decrypt var
@tmp varbinary(256) -- linked server pswd decrypt var
select @allsetopt=number from master.dbo.spt_values
where type = 'A' and name = 'ALL SETTABLE OPTIONS' -- Only 7.0 else use 4063
declare d cursor for SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource,
location, providerstring, catalog, srvnetname
from master..sysservers
where srvid > 0 -- Local Server
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
PRINT '--------------------------------'
Print '-- ' + @server
PRINT '--------------------------------'
If @status in (64,65) --Remote Server
Begin
Print 'sp_addserver'
Print ' @server = '''+ @server + ''''
Print ' GO'
If @status = 64
Begin
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc'','
Print ' @optvalue = ''false'''
Print ' 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
Print 'sp_addremotelogin'
Print ' @remoteserver = '''+ @server + ''','
Print ' @loginame = '''+ @loclogin + ''','
Print ' @remotename = '''+ @rmtlogin + ''''
Print ' GO'
fetch next from r into @loclogin, @rmtlogin
end
close r
deallocate r
End
Else --Linked server
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'
PRINT 'sp_addlinkedserver'
Print ' @server = '''+ @server + ''''
Print ', @srvproduct = ''' + @srvproduct + ''''
If @srvproduct <> 'SQL Server' --Cannot specify additional info for SQL Server Product
Begin
Print ', @provider = ''' + @provider + ''''
Print ', @datasrc = ''' + @datasrc + ''''
Print ', @location = ''' + @location + ''''
Print ', @provstr = ''' + @provstr + ''''
Print ', @catalog = ''' + @catalog + ''''
End
Print ' GO'
-- Set all servers options to false, then reset correct server options
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc'','
Print ' @optvalue = ''false'''
Print ' GO'
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc out'','
Print ' @optvalue = ''false'''
Print ' GO'
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''data access'','
Print ' @optvalue = ''false'''
Print ' GO'
declare s cursor for SELECT srvoption
from #tmpsrvoption
open s
fetch next from s into @srvoption
while (@@FETCH_STATUS<>-1)
begin
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = '''+ @srvoption + ''','
Print ' @optvalue = ''true'''
Print ' GO'
fetch next from s into @srvoption
End
close s
deallocate s
--Script linked server logins
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
-- Use self no remote user/password
If (@selfstatus = 1 and @loclogin is null)
Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''true'''
Print ' GO'
End
Else
If (@selfstatus = 1 and @loclogin is not null) Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''true'','
Print ' @locallogin = '''+ @loclogin + ''','
Print ' @rmtuser = NULL,'
Print ' @rmtpassword = NULL'
Print ' GO'
End
Else
If (@selfstatus = 0 and @rmtlogin is null) Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''false'','
Print ' @locallogin = NULL,'
Print ' @rmtuser = NULL,'
Print ' @rmtpassword = NULL'
Print ' GO'
End
Else
If (@selfstatus = 0) Begin -- Check for Use self mappings
exec ('declare pwd cursor for
select l.password from master..sysservers s
join master..sysxlogins l on s.srvid = l.srvid --where l.sid is not null
where s.srvname = '''+ @server + ''' and l.name = '''+ @rmtlogin + '''')
-- Decrypt passwords
-- Only works for 7.0 server
-- Encrypt algorithm changed in 2000
open pwd
fetch next from pwd into @rmtpass
while @@fetch_status = 0
begin
set @i = 0
set @pwdtext = N''
while @i < datalength(@rmtpass)
begin
set @tmp = encrypt(@pwdtext + nchar(0))
set @lsb = convert(tinyint, substring(@tmp, @i + 1, 1))
^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
set @i = @i + 1
set @tmp = encrypt(@pwdtext + nchar(@lsb))
set @msb = convert(tinyint, substring(@tmp, @i + 1, 1))
^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
set @i = @i + 1
set @pwdtext = @pwdtext + nchar(convert(smallint, @lsb)
+ 256 * convert(smallint, @msb))
end
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''false'','
If (@loclogin is null)
Begin
Print ' @locallogin = NULL,'
End
Else
Begin
Print ' @locallogin = '''+ @loclogin + ''','
End
If (@rmtlogin is null)
Begin
Print ' @rmtuser = NULL,'
End
Else
Begin
Print ' @rmtuser = '''+ @rmtlogin + ''','
End
If (@pwdtext is null)
Begin
Print ' @rmtpassword = NULL'
End
Else
Begin
print ' @rmtpassword = '''+ @pwdtext + ''''
End
Print ' GO'
fetch next from pwd into @rmtpass
end
close pwd
deallocate pwd
End
fetch next from ll into @loclogin, @selfstatus, @rmtlogin
End
close ll
deallocate ll
End
If @netname <> @server -- If the srvnetname.sysservers is different from srvname.sysservers
Begin
Print 'sp_setnetname'
Print ' @server = '''+ @server + ''','
Print ' @network_name = '''+ @netname + ''''
End
fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc,
@location, @provstr, @catalog, @netname
End
close d
deallocate d
October 31, 2007 at 9:46 pm
Hi Maverick,
The script works however the remote password is having chinese characters and all our password is in English character.
--------------------------------
-- APZDDB04\INSTANCE2
--------------------------------
sp_addlinkedserver
@server = 'APZDDB04\INSTANCE2'
, @srvproduct = 'SQL Server'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'rpc',
@optvalue = 'false'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'rpc out',
@optvalue = 'false'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'data access',
@optvalue = 'false'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'rpc',
@optvalue = 'true'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'rpc out',
@optvalue = 'true'
GO
sp_serveroption
@server = 'APZDDB04\INSTANCE2',
@optname = 'data access',
@optvalue = 'true'
GO
sp_addlinkedsrvlogin
@rmtsrvname = 'APZDDB04\INSTANCE2',
@useself = 'true'
GO
sp_addlinkedsrvlogin
@rmtsrvname = 'APZDDB04\INSTANCE2',
@useself = 'false',
@locallogin = 'eworkadm',
@rmtuser = 'PCUser',
@rmtpassword = '??????'
GO
December 22, 2008 at 8:18 am
I know this is a really old post, but thougth I'd point out a couple lines in the script:
-- Decrypt passwords
-- Only works for 7.0 server
-- Encrypt algorithm changed in 2000
In my case I know what the remote passwords would have to be. If I come see a solution in 2000 I'll try to remember to post it here.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 22, 2008 at 2:54 pm
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy