Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How about something like Idera's user cloner? I bet that would do it. –  Eric Higgins Mar 6 '12 at 20:56
    
@EricHiggins There are other objects I was hoping to bring over than just users and permissions, such as SQL jobs, linked servers, and aliases. –  Rachel Mar 6 '12 at 21:17
    
Jobs are in MSDB. Idera also has a job mover, but I don't recall any utils for LS or aliases. –  Eric Higgins Mar 6 '12 at 21:29

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer

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_%' 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.