locked
Restoring DB from one server to another server RRS feed

  • Question

  • Hi,

    I just restored database from sql 2008 to Sql 2014, both are on same domain.

    Do i need to do with login as users are complaining?

    I thought when both the servers are on same domain then i don't need to match SID for same domain.

    After migration what other steps needs to be applied?

    Thanks for your help!

    Friday, July 31, 2020 9:06 PM

All replies

  • The SID for Windows accounts will match but you still need to create the the Windows login (or login for the Windows Group) on the target server in order for the database users to have permissions to connect to the instance.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 1, 2020 2:25 AM
  • Thanks Dan.

    So i need to create Login and user both, right?

    What;s the best way to migrate jobs?

    Sunday, August 2, 2020 4:31 PM
  • Hi pdsqsql,

    >So i need to create Login and user both, right?

    Right. And if the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists). Please reference: restore_permissions

    More information :backup-restore

    >What;s the best way to migrate jobs?

    Actually, there are many ways to import SQL Server 2008 Database to SQL Server 2014. And this could be helpful: upgrading-from-sql-server-2008-or-sql-server-2008-r2

    BR,

    Mia




    • Edited by MIAOYUXI Monday, August 3, 2020 5:55 AM
    Monday, August 3, 2020 5:49 AM
  • Hi pdsqlsql,

    Is the reply helpful?

    BR,

    Mia

    Tuesday, August 4, 2020 12:36 AM

  • So i need to create Login and user both, right?

    What;s the best way to migrate jobs?

    For Windows users/groups, you only need to create the Windows logins. The users will already exist in the restored database and the SID will match the newly created login as determined by AD.

    There is more than one way to migrate jobs but an easy method for most cases is tot simply script the jobs from SSMS and run the script on the target server.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, August 4, 2020 9:36 AM
  • Hi pdsqlsql,

    Is the reply helpful?

    If the response helped, do "Accept Answer" and upvote it.

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 1:15 AM
  • Hi pdsqsql,

    Is the reply helpful?

    If the response helped, do "Accept Answer" and upvote it.

    BR,

    Mia


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 1:18 AM
  • Thanks Mia.

    The user who is migrating is part SysAdmin so it won;t be a issue.

    I will check the Urls.

    appreciate for your help!

    Thursday, August 6, 2020 9:26 PM
  • Thanks Dan, I though the same but was confused and i was getting error as i didn't create the login.

    When i tried to create the login, getting error 'Login Already Exists", then i have to go to the login and map with Role for that Database

    Thursday, August 6, 2020 9:28 PM