还有登录帐号要过去呀,那就要牵涉到master数据库的备份还原了 开始运行cmd,在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m 其中:-c 可以缩短启动时间,SQL Server 不作为Windows NT的服务启动 -f 用最小配置启动SQL Server -m 单用户模式启动SQL Server
单用户模式启动后就可以.进行master数据库的恢复 了,进入查询分析器,有个提示不要理会它, 输入恢复语句进行数据库恢复: RESTORE DATABASE master from disk='c:\具体的备份文件名' 还原成功。
database\MSSQL\Data\ 下的MDF以及LDF文件对应拷贝过去即可
--直接拷贝mdf ldf,然后附加
1.一台原服务器是SCSI硬盘,另一台是联想服务器(非SCSI),GHOST可能不行!! 2.前者服务器是在线的,想用“分离也是不行的”; 3.只是一个库一个库的备份到另一台,关键是master,下面是我搜来的文章,不知行否 如何导入不同版本的SQLServer的master表备份 如果尝试在不同版本的 SQL Server 上恢复 master 表,会出现以下错误: 服务器: 消息 3168,级别 16,状态 1,行 1 无法还原设备 D:\BACKUP\MASTER.BAK 上的系统数据库备份,因为创建该数据库的服务器版本(134218488)与此服务器的版本(134217922)不同。 服务器: 消息 3013,级别 16,状态 1,行 1 RESTORE DATABASE 操作异常终止。 -------------------------------------------------------------------------------- 解决办法:用 RESTORE DATABASE 的 WITH MOVE 参数则可。 具体操作: RESTORE DATABASE mas_bak FROM disk='d:\backup\master.bak' WITH MOVE 'master' TO 'd:\data\master.mdf', MOVE 'mastlog' TO 'd:\data\master.ldf' GO BACKUP DATABASE mas_bak TO disk='d:\backup\master.bak.new' WITH FORMAT GO 注:第一句restore将d:\backup\master.bak(原master的备份)还原成一个新数据库mas_bak,并且 将mas_bak主库(即逻辑名'master')设为d:\data\master.mdf, 将mas_bak副库(即逻辑名'mastlog')设为d:\data\master.ldf; 下一个backup语句将新库mas_bak备份到d:\backup\master.bak,后面将用到它; //------------------------------------------------------------------------------------ sqlservr.exe -c -m 单用户模式启动SQL Server 在计算机管理"服务"中停止mssqlserver,并将启动参数设为 -c -m,启动RESTORE DATABASE master FROM disk='d:\backup\master.bak.new' WITH REPLACE, MOVE 'master' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf', MOVE 'mastlog' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf' GO 注:restore 语句将前面的备份库 d:\backup\master.bak.new 用覆盖方式恢复到master, 并将主库文件设为 c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf(即原位置) 将副库文件设为 c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf(即原位置) //------------------------------------------------------------------------------------ 要注意master原位置,在WINCC实例下须: RESTORE DATABASE master FROM disk='d:\backup\master.bak.new' WITH REPLACE, MOVE 'master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$WINCC\Data\master.mdf', MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$WINCC\Data\mastlog.ldf' GO //------------------------------------------------------------------------------------------若有需要修改sysdatabases和sysdevices表各系统数据库的目录为新服务器的目录,可作如下操作。以model库为例: exec sp_configure 'allow updates',1 reconfigure with override go update mas_bak.dbo.sysdatabases set filename='d:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf' where name='model' update mas_bak.dbo.sysdevices set phyname='d:\Program Files\Microsoft SQL Server\MSSQL\data\modellog.ldf' where name='modellog' go exec sp_configure 'allow updates',0 reconfigure with override go 欢迎继续探讨
/* 说明:SQL SERVER 2005 迁移登录名-->2005 or 2005-->2008 环境 1,在Master 建立两个存储过程,如下:sp_hexadecimal,sp_help_revlogin 2, 执行存储过程:EXEC sp_help_revlogin 3,在新实例上执行第步得到的文本 4,use 迁移的数据库;使用户名绑定到登陆名上EXEC sp_change_users_login 'Update_One', 'username', 'loginname'; author:刘朝文 2010-07-28 */ USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 ENDSELECT @hexvalue = @charvalue GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3)DECLARE @defaultdb sysname
IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO -------------------------------------------------------------------------------------------------------------------- /* 说明:SQL SERVER 2000 环境 http://support.microsoft.com/kb/246133 1,在Master 建立两个存储过程,如下:sp_hexadecimal,sp_help_revlogin 2, 执行存储过程:EXEC sp_help_revlogin 3,在新实例上执行第步得到的文本如:CREATE LOGIN [regin_role] WITH PASSWORD = 0x01008DA28EDD1E4AAD258B381E61459DDD6FA5FB26757D15758C HASHED, SID = 0xC63AB2B8C5FE794B9EB1156C074D4079, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF 4,use [迁移的数据库];使用户名绑定到登陆名上EXEC sp_change_users_login 'Update_One', 'username', 'loginname'; */ ----- Begin Script, Create sp_help_revlogin procedure -----USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUT AS DECLARE @charvalue varchar(256) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256)IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' PRINT 'DECLARE @pwd sysname' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstr EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null password EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + '''skip_encryption_old''' ELSE SET @tmpstr = @tmpstr + '''skip_encryption''' PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO ----- End Script -----
奶乃的,目的机的SQL2000SERVER的master更新后,已经启不来了。 原因是两台机的版本相差太大了。 源机: xp-sql2000server for wincc (工控wincc专用sql2000server版) 目的机: win2003server -sql 2000 server
2、直接备份还原到另外一台机器。
这是重点!!
开始运行cmd,在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m 其中:-c 可以缩短启动时间,SQL Server 不作为Windows NT的服务启动 -f 用最小配置启动SQL Server -m 单用户模式启动SQL Server
单用户模式启动后就可以.进行master数据库的恢复 了,进入查询分析器,有个提示不要理会它, 输入恢复语句进行数据库恢复: RESTORE DATABASE master from disk='c:\具体的备份文件名' 还原成功。
--直接拷贝mdf ldf,然后附加
2.前者服务器是在线的,想用“分离也是不行的”;
3.只是一个库一个库的备份到另一台,关键是master,下面是我搜来的文章,不知行否
如何导入不同版本的SQLServer的master表备份
如果尝试在不同版本的 SQL Server 上恢复 master 表,会出现以下错误:
服务器: 消息 3168,级别 16,状态 1,行 1 无法还原设备 D:\BACKUP\MASTER.BAK 上的系统数据库备份,因为创建该数据库的服务器版本(134218488)与此服务器的版本(134217922)不同。 服务器: 消息 3013,级别 16,状态 1,行 1 RESTORE DATABASE 操作异常终止。 --------------------------------------------------------------------------------
解决办法:用 RESTORE DATABASE 的 WITH MOVE 参数则可。
具体操作: RESTORE DATABASE mas_bak FROM disk='d:\backup\master.bak' WITH MOVE 'master' TO 'd:\data\master.mdf', MOVE 'mastlog' TO 'd:\data\master.ldf'
GO
BACKUP DATABASE mas_bak TO disk='d:\backup\master.bak.new' WITH FORMAT
GO 注:第一句restore将d:\backup\master.bak(原master的备份)还原成一个新数据库mas_bak,并且
将mas_bak主库(即逻辑名'master')设为d:\data\master.mdf,
将mas_bak副库(即逻辑名'mastlog')设为d:\data\master.ldf;
下一个backup语句将新库mas_bak备份到d:\backup\master.bak,后面将用到它;
//------------------------------------------------------------------------------------
sqlservr.exe -c -m 单用户模式启动SQL Server
在计算机管理"服务"中停止mssqlserver,并将启动参数设为 -c -m,启动RESTORE DATABASE master FROM disk='d:\backup\master.bak.new' WITH REPLACE, MOVE 'master' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf', MOVE 'mastlog' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf'
GO
注:restore 语句将前面的备份库 d:\backup\master.bak.new 用覆盖方式恢复到master,
并将主库文件设为 c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf(即原位置)
将副库文件设为 c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf(即原位置)
//------------------------------------------------------------------------------------
要注意master原位置,在WINCC实例下须:
RESTORE DATABASE master FROM disk='d:\backup\master.bak.new' WITH REPLACE, MOVE 'master' TO
'C:\Program Files\Microsoft SQL Server\MSSQL$WINCC\Data\master.mdf',
MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$WINCC\Data\mastlog.ldf'
GO
//------------------------------------------------------------------------------------------若有需要修改sysdatabases和sysdevices表各系统数据库的目录为新服务器的目录,可作如下操作。以model库为例: exec sp_configure 'allow updates',1 reconfigure with override go update mas_bak.dbo.sysdatabases set filename='d:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf' where name='model' update mas_bak.dbo.sysdevices set phyname='d:\Program Files\Microsoft SQL Server\MSSQL\data\modellog.ldf' where name='modellog' go exec sp_configure 'allow updates',0 reconfigure with override go
欢迎继续探讨
说明:SQL SERVER 2005 迁移登录名-->2005 or 2005-->2008 环境
1,在Master 建立两个存储过程,如下:sp_hexadecimal,sp_help_revlogin
2, 执行存储过程:EXEC sp_help_revlogin
3,在新实例上执行第步得到的文本
4,use 迁移的数据库;使用户名绑定到登陆名上EXEC sp_change_users_login 'Update_One', 'username', 'loginname';
author:刘朝文 2010-07-28
*/
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
ENDSELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--------------------------------------------------------------------------------------------------------------------
/*
说明:SQL SERVER 2000 环境
http://support.microsoft.com/kb/246133
1,在Master 建立两个存储过程,如下:sp_hexadecimal,sp_help_revlogin
2, 执行存储过程:EXEC sp_help_revlogin
3,在新实例上执行第步得到的文本如:CREATE LOGIN [regin_role] WITH PASSWORD = 0x01008DA28EDD1E4AAD258B381E61459DDD6FA5FB26757D15758C HASHED, SID = 0xC63AB2B8C5FE794B9EB1156C074D4079, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
4,use [迁移的数据库];使用户名绑定到登陆名上EXEC sp_change_users_login 'Update_One', 'username', 'loginname';
*/
----- Begin Script, Create sp_help_revlogin procedure -----USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GOIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
原因是两台机的版本相差太大了。
源机: xp-sql2000server for wincc (工控wincc专用sql2000server版)
目的机: win2003server -sql 2000 server
找两个版本相差小的试试
sp_change_users_login'autofix''loginname'