Can I move a system database from one version of SQL server to another?

Question

We are moving our databases from one computer running SQL Server 2005 to a different machine running SQL Server 2008.

I am following the steps found here, in an attempt to copy over the master database, with all the user logins and server-level permissions, however it is telling me the backup cannot be restored because it was created by a different version of the server.

Is there a way to copy the master database from one version of SQL server to another version of SQL server?

Answer 1

No you can not move the master database (or any system database) from one version of SQL Server to another version of SQL Server. You would need to install SQL 2005 on the server, then restore master. Then upgrade to SQL 2008.

Answer 2

I could be wrong but I don't think you can get there (I've never tried it). To move logins from a source to a target server, I use Microsoft's sp_help_revlogin. To move database users I use the following script (not mine, got it off the web):

SET NOCOUNT ON

SELECT  'USE ' + QUOTENAME(DB_NAME()) 

SELECT  'CREATE USER ' +  QUOTENAME(USER_NAME(rm.member_principal_id)) 
FROM sys.database_role_members AS rm 
WHERE  USER_NAME(rm.member_principal_id) IN 
    (  
        --get user names on the database
        SELECT [name]
        FROM sys.database_principals 
        WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
        and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
    )
ORDER BY rm.role_principal_id ASC


SELECT  'EXEC sp_addrolemember @rolename ='
        + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') 
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN 
    (  
            --get user names on the database
            SELECT [name]
            FROM sys.database_principals
            WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
            and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
      )

SELECT  
    CASE 
        WHEN perm.state <> 'W' THEN perm.state_desc 
        ELSE 'GRANT'
    END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
    + CASE
                    WHEN cl.column_id IS NULL THEN SPACE(0)
                    ELSE '(' + QUOTENAME(cl.name) + ')'
      END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
    + CASE 
        WHEN perm.state <> 'W' THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
    END

FROM   sys.database_permissions AS perm
INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE obj.name not like 'dt_%'