sqlserver2008 修改服务器名,sqlserver2008修改服务器名后无法登录

原因

服务器名修改后,sqlserver表中保存的服务器名并不会跟着修改,需要手动修改

方法

用sa账号登录后可以对数据库操作,但是不能使用发布订阅等功能

查看原服务器名和新服务器名,如果相同,可以正常登录

select @@servername

select serverproperty('servername')

手动将原服务器名修改为新服务器名,成功后重启sqlserver服务,重复步骤2

--将两者协调一致, 再重启 SQL Server 服务

if serverproperty('servername') <> @@servername

begin

declare @server sysname

set @server = @@servername

exec sp_dropserver @server = @server

set @server = cast(serverproperty('servername') as sysname)

exec sp_addserver @server = @server , @local = 'LOCAL'

END

过程不顺利的解决办法

报错

Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56

There are still remote logins or linked logins for the server 'oldServerName'.

查看当前所有连接

-- Query to check remote login

select

srl.remote_name as RemoteLoginName,

sss.srvname

from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid

断开连接

-- Query to remove the remote login

--Default Instance

sp_dropremotelogin 'oldServerName'

GO

--Named Instance

sp_dropremotelogin 'oldServerName\instancename'

GO

报错

There is no remote user '(null)' mapped to local user '(null)' from the remote

server 'oldServerName'.

查看发布订阅

-- Query to check if the SQL Instance databases are involved in replication

select name,is_published,is_subscribed,is_distributor from sys.databases

where is_published = 1 or is_subscribed =1 or is_distributor = 1

GO

如果有数据

列出发布的服务器名和发布数据库

sp_helpdistpublisher

列出所有subscribers.

sp_helpsubscriberinfo

删除所有subscribers,每一个都要执行一次

sp_dropsubscriber 'SubscriberName'

列出所有publishers

sp_helppublisher

删除所有publishers,每一个都要执行一次

sp_dropdistpublisher 'publishersName'

报错

Invalid object name 'dbo.MSmerge_agents'

按表语句在distribution建表

create table msmerge_agents

(id int, name nvarchar(100),

publisher_id smallint,

publisher_db sysname, publication sysname,

subscriber_id smallint, subscriber_db sysname,

local_job bit, job_id binary(16),

profile_id int, anonymous_subid uniqueidentifier,

subscriber_name sysname,

creation_date datetime,

offload_enabled bit,

offload_server sysname,

sid varbinary(85),

subscriber_security_mode smallint,

subscriber_password nvarchar(524),

publisher_login sysname,

publisher_password nvarchar(524),

job_step_uid uniqueidentifier

)

CREATE table MSmerge_subscriptions

(

publisher_id smallint,

publisher_db sysname,

publication_id int,

subscriber_id smallint,

subscriber_db sysname,

subscription_type int,

sync_type tinyint,

status tinyint,

subscription_time datetime

)

执行

exec sp_dropdistributiondb @database = N'distribution'

GO

重复步骤3

参考文档

https://blog.csdn.net/godbugs/article/details/50232749

http://www.itkeyword.com/doc/1014338758769575x776/system-query-sql%20serverserversqlserverdatabase

https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

http://blog.chinaunix.net/uid-16909016-id-120125.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值