Move two existing databases into new SQL Server

Question

Hi everyone,

I'm in the process of moving two existing databases to a brand new SQL Server 2008 R2 server.  The existing databases are on separate servers today (2008 R2 as well).  I need to take a backup of both databases and restore them to the new SQL Server instance.  My question is - how can I restore all users and roles for both databases?  I am using SQL Server Authentication for my logins.  How can I retain all existing users, logins, roles, and object permission during the restore process?  Will security and permissions be restored from a backup?

Answer 1

Hello,

Please use the following articles to transfer logins from the old instance to the new one:

http://support.microsoft.com/kb/918992

http://www.sql-server-performance.com/2009/transfer-logins-transfer-database-task-ssis/    

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Please follow the Albert instructions, so let me tel few information-

Once you restored the database,the same data will exists on the destination side as well,but the logins will not create on the destination instead of Database users will be creates , so in order to map login you need to script out the logins & SIDs then execute the rev logins scripts(please check Alert KB).

Ensure you have the proper permission to restore.

SQL Script to generate script for existing database permissions
http://www.sqlserver-training.com/sql-script-to-generate-script-for-existing-database-permissions/-

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.