Hi All,

I have to restore the databases using backup of different server. Investigating about the steps to follow after restoring the DB. CAn anyone help?

Hi Swapna,

Please run the below script, copy all permissions and apply after refresh on target.

--To generate the script for creating the user

select distinct 'CREATE USER ' + b.name + '  FOR LOGIN  '  + b.name + '  WITH DEFAULT_SCHEMA=dbo'
from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R'

-- script for Fixing the orphan users
select distinct 'exec sp_change_users_login '+ '''' + 'Auto_Fix' +'''' +','+ ''''+ b.name + ''''
from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R'


--To generate the script for granting the role to the users

select distinct 'EXEC sp_addrolemember '+ '''' + c.name+''', ' + '''' + b.name + ''''
from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R'

Hi Swapna,

This will depend on, if you are restoring the backup on an existing database replacing it or the restoring the backup as  a new database.

If restoring on an existing database you have to capture the database permissions on the current copy and re-apply them back after restore.

If it is a brand new database all you need to worry about is fixing orphaned users if any

Hope this helps

Cheers

Bhanu

• Edited by bhanu_nz Tuesday, February 24, 2015 3:52 AM
• Proposed as answer by Michelle LiMicrosoft contingent staff, Moderator Tuesday, February 24, 2015 1:41 PM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Tuesday, March 03, 2015 8:06 AM

Hi All,

I have to restore the databases using backup of different server. Investigating about the steps to follow after restoring the DB. CAn anyone help?

The most important thing is to find orphaned users, make sure you fix them.

https://msdn.microsoft.com/en-us/library/ms175475.aspx?f=255&MSPPError=-2147217396

The other post restore script depends on your environment. Generally people also run some security scripts which we cannot tell

I dont agree to rebuild of stats after restore because stats are stored in same database so they are not going to change as such and they would be there after restore is done

Agreed stats goes along with backup, if the statistics are in good shape when the backup was taken, they'll be in good shape when the database is restored. If the statistics were out of date or problematic when the backup was taken, they'll be in that exact same circumstance when the database is restored.

Also as I am not sure if database was restored from same version. Queries might return differing results from the original, when restoring a database to a later version. As the post did not mention any SQL version details I suggested in general. 

Also as I am not sure if database was restored from same version. Queries might return differing results from the original, when restoring a database to a later version. As the post did not mention any SQL version details I suggested in general. 

Hi All,

I have to restore the databases using backup of different server. Investigating about the steps to follow after restoring the DB. CAn anyone help?

Hi,

Restoring a database from backup on an existing database replacing it or the restoring the backup as  a new database.

If restoring on an existing database you have to capture the database permissions on the current copy and re-apply them back after restore.

If it is a brand new database all you need to worry about is fixing orphaned users if any

Also depending on any triggers, replication enabled on the database.

Thanks

• Proposed as answer by DawnYu 3 hours 55 minutes ago