linux sql server 迁移,SQL Server2008 实例数据迁移步骤

原实例和新实例环境相同，均为Windows Server 2008 Cluster加SQL Server 2008 SP1。

这次做的是将旧实例的数据迁移至新实例。

一、新的SQLServer实例安装

安装的是SQL Server 2008 SP1，RTM合并SP1步骤如下：

1.将SQL Server 2008 RTM安装介质拷贝到c:\SQLServer2008_FullSP1；

2.下载SP1补丁文件，并解压到c:\SQLServer2008_FullSP1\PCU：

SQLServer2008SP1-KB968369-x64-ENU.exe

/x:c:\SQLServer2008_FullSP1\PCU

3.将setup.exe和setup.rll文件copy出来并覆盖原有的介质

robocopy C:\SQLServer2008_FullSP1\PCU

c:\SQLServer2008_FullSP1 Setup.exe

robocopy C:\SQLServer2008_FullSP1\PCU

c:\SQLServer2008_FullSP1 Setup.rll

4.将x64文件夹里的所有文件除了Microsoft.SQL.Chainer.PackageData.dll，copy覆盖到原有安装介质相应位置，可通过下面代码实现：

robocopy C:\SQLServer2008_FullSP1\pcu\x64

C:\SQLServer2008_FullSP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll

如果没有排除此dll文件，在打开setup.exe的时候会有如下报错：

The specified action LandingPage is not

supported for the SQL Server patching operation.

Error code 0x84BF0007

可用老版本恢复回去再运行。

5.如果在x64目录下有Defaultsetup.ini文件，需要在最后加上一行PCUSOURCE=".\PCU"，例如：

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

...

PCUSOURCE=".\PCU"

如没有此文件，创建该文件，并写入如下内容：

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

PCUSOURCE=".\PCU"

Windows Cluster下安装实例和加节点命令：

Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

Setup /SkipRules=Cluster_VerifyForErrors

/Action=AddNode

二、迁移login

原实例上执行以下脚本，导出login信息：

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

END

SELECT @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_varbinaryvarbinary (256)

DECLARE @PWD_stringvarchar (514)

DECLARE @SID_varbinary

varbinary (85)

DECLARE @SID_string varchar

(514)

DECLARE @tmpstrvarchar (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_curs

FETCH 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

导出login信息：

EXEC sp_help_revlogin;

三、导出Server Role信息

SELECTr.name AS SERVER_roles

,

p.name AS [Login_Name]

FROMsys.server_principals r

INNER JOIN

sys.server_role_members m

ONr.principal_id = m.role_principal_id

INNER JOIN

sys.server_principals p

ONp.principal_id = m.member_principal_id

WHERE(r.type = 'R')

ORDER BY server_roles ASC

新实例上增加Server Role步骤：

方法1：打开SQL Server

Management Studio -> Security -> Logins

双击登录用户-> Server

Roles ->勾选角色-> OK

方法2：打开SQL Server

Management Studio -> Security -> Server Roles

双击角色-> General

-> Add

添加登录用户信息，可使用Check

Names验证输入是否正确-> OK

四、恢复数据库

backup database

to

disk = 'I:\database_1.bak',

disk = 'I:\database_2.bak',

disk = 'I:\database_3.bak',

disk = 'I:\database_4.bak'

with compression , stats=5

go

恢复后，一般还会有些个性化参数需要调整，比如数据库内存等，这个可根据实际情况设置调整。