You are here:   Blog
Register   |  Login

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords


Awared MVP 


Microsoft® Community Contributor 

Microsoft® Community Contributor

 Read first, before you you use the blog! Maximize

Written by: ronen ariely
29/06/2012 20:41 RssIcon

העתקת שרת SQL קיים לשרת חדש

שלבי עבודה מרכזיים [נכתב על ידי אריאלי רונן לשימוש פנימי]

קישור להורדה של המסמך בתחתית העמוד, מומלץ לבדוק גרסאות חדשות בקישור לפני השימוש

1.       Security objects - Logins, Users

1.1.    transferring the logins and the passwords

Option 1: We can transfer each LOGIN manually using this short procedure:

--> 1. Returns all LOGINs (or the specific that you want to copy) and server roles defined on the server.
SELECT * FROM  sys.sql_logins
-- Each LOGIN must have a uniqe SID
-- Creating users in two instances of SQL Server will not create the same SID even if you use the same LOGIN name and passward!
--> 2. Copy the user SID that you want to copy to the new serrver.
--  0x00C9D664AF756647B9A84C430CBFEFBB
--> 3. Check the Hashed passward of the user and copy the value
    LOGINPROPERTY (N'LoginA' , N'PasswordHash') as Hash_Password
-- 0x02004972B36F49F5FDF777F510769402CA5955DCD1D6CE4E75451FEE2AE24FBD908E622739F589B1E7899A4E505EEEACD793C9C1191E7DBED8E01FD4F968F8DC93B10BF93049
--> 4. Open the new server
--     and create new user with the same passwward and SID using the information from the old server
    WITH PASSWORD = 0x02004972B36F49F5FDF777F510769402CA5955DCD1D6CE4E75451FEE2AE24FBD908E622739F589B1E7899A4E505EEEACD793C9C1191E7DBED8E01FD4F968F8DC93B10BF93049
    SID = 0x00C9D664AF756647B9A84C430CBFEFBB

Option 2: Or we can use Microsoft Procedure to transfer all LOGIN in one time

1.1.1. create stored procedures sp_hexadecimal and the sp_help_revlogin

1.1.2. Execute the sp_help_revlogin to generate login script.

1.1.3. Run the generated login script on the new server (after checking it manually!)

1.2.    Server Level Security

    principal_name = QUOTENAME(spl.name),
    sp.state_desc + N' ' + sp.permission_name + N' TO ' + cast(QUOTENAME(spl.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) AS "T-SQL Script"
FROM sys.server_permissions sp
inner join sys.server_principals spl on (sp.grantee_principal_id = spl.principal_id)
    spl.name not like '##%' -- skip PBM accounts
    and spl.name not in ('dbo', 'sa', 'public')
order by sp.permission_name, spl.name

1.3.    Server Level Roles

    QUOTENAME(sp.name) AS "ServerRoleName",
    sp.type_desc AS "RoleDescription",
    QUOTENAME(m.name) AS "PrincipalName",
    m.type_desc AS "LoginDescription",
    'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + sp.name + '''' AS "T-SQL Script"
FROM sys.server_role_members AS srm
    inner join sys.server_principals sp on (srm.role_principal_id = sp.principal_id)
    inner join sys.server_principals m on (srm.member_principal_id = m.principal_id)
    sp.is_disabled = 0
    and m.is_disabled = 0
    and m.name not in ('dbo', 'sa', 'public')
    and m.name <> 'NT AUTHORITY\SYSTEM'

1.4.    Database Level Security

    QUOTENAME(dpl.nameAS 'principal_name',
     dp.state_desc + N' ' + dp.permission_name + N' TO ' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500))  AS "T-SQL Script"
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
    and dpl.name not like '##%' -- excluds PBM accounts
    and dpl.name not in ('dbo', 'sa', 'public')
ORDER BY dp.permission_name ASC, dp.state_desc ASC

1.5.    Database Level Roles

    QUOTENAME(drole.name) as "DatabaseRoleName",
    QUOTENAME(dp.name) as "PrincipalName",
    'EXEC sp_addrolemember @membername = N''' + dp.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + drole.name + '''' AS "T-SQL Script"
FROM sys.database_role_members AS drm
inner join sys.database_principals drole on (drm.role_principal_id = drole.principal_id)
inner join sys.database_principals dp on (drm.member_principal_id = dp.principal_id)
where dp.name not in ('dbo', 'sa', 'public')

1.6.    Database Level Explicit Permissions

    dp.state_desc AS "StateDescription" ,
    dp.permission_name AS "PermissionName" ,
    SCHEMA_NAME(obj.schema_id) AS [Schema Name],
    obj.NAME AS [Object Name],
    QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + CASE WHEN col.column_id IS NULL THEN SPACE(0)           ELSE '(' + QUOTENAME(col.name COLLATE DATABASE_DEFAULT) + ')'      END AS "ObjectName" ,
    QUOTENAME(dpl.name COLLATE database_default) AS "UserName" ,
    dpl.type_Desc AS "UserRoleType" ,
    obj.type_desc AS "ObjectType" ,
    dp.state_desc + N' ' + dp.permission_name + N' ON '    + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + N' TO ' + QUOTENAME(dpl.name COLLATE database_default) AS "T-SQL Script"
FROM    sys.database_permissions AS dp
INNER JOIN sys.objects AS obj ON ( dp.major_id = obj.[object_id] )
INNER JOIN sys.database_principals AS dpl ON ( dp.grantee_principal_id = dpl.principal_id )
LEFT JOIN sys.columns AS col ON ( col.column_id = dp.minor_id  AND col.[object_id] = dp.major_id)
    obj.name NOT LIKE 'dt%'
    AND obj.is_ms_shipped = 0
    AND dpl.name NOT IN ( 'dbo', 'sa', 'public' )
ORDER BY    dp.permission_name ASC ,    dp.state_desc ASC

1.7.    Orphaned users – Check and Fix [The code below 2.3,2.4]

1.8.    Validate User Accounts – try to use each User Account

2.       Databases

2.1.    Backup all databases on the old server

2.2.    Restore all database on the new server

2.3.    Detect Orphaned Users (Foreach database)

-- usint the sp_foreachdb writen by Aaron Bertrand
EXEC sp_foreachdb
    @command = N'print ''use ?; exec sp_change_users_login @Action=''''Report'''';'''
    --,@suppress_quotename = 1 -- ביטול ההצגה של הסוגריים המרובעים
    ,@recovery_model_desc = N'SIMPLE' -- סינון רק מסדי נתונים במוד מסויים
    ,@user_only = 1 -- סינון רק מסדי נתונים של משתמשים ללא מסדי נתונים של המערכת

2.4.    Fix Orphaned Users if needed

-- If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
-- If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'password'

3.       Jobs

3.1.    Generate SQL script

3.1.1. Enable Object explorer detail.

3.1.2. In SQL Server 2005 or earlier:  goto view->Summary
In SQL Server 2008 or older:  goto view-> Object Explorer Details

3.1.3. Expand SQL Server Agent and click on Jobs.

3.1.4. After selecting all the jobs (together using control) right click -> Script Job As -> Create To -> and select the desired location where you want to script all the selected jobs

4.       Server configuration attributes (sp_configure(

4.1.    Create Script for the configuration of the new server

USE master;
EXEC sp_configure 'show advanced option', '1';
declare @table as table(
name nvarchar(100),
minimum int,
maximum int,
config_value int,
run_value int
insert @table
EXECUTE sp_executesql sp_configure
select 'EXEC sp_configure ''' + name + ''', ''' + convert(char(10),config_value) + ''';' from @table

5.       Security objects - Permission, Credentials and Audit configuration


6.       Other objects such as Linked servers, Mail profiles


7.       Replication information


COPY SQL instance to a new Instance -- Download