Hi,
Off late I have been doing a lot of db refresh on adhoc basis but I tend to do some mistakes each time. So I thought of tabulating the steps in one central point. I hope some of you can also benefit from it. Below mentioned are logical steps and some scripts to make things easier.
Steps for refresh:-
>take backup from prod
>zip the bakcup
>move the zip to UAT server
>unzip the backupfile
>take backup of the database on UAT and keep it in a separate folder just in case of roll back
>run
[restore filelistonly from device ] and [sp_helpdb ] and check that both have matching files.
>script out the roles access rights on UAT database
>script out the user access rights on UAT database
>kill all user connections on the database on UAT
>restore the database on UAT
>delete the users exect dbo,sa,guest and other
>run the user access script
>use sp_change_users_login 'report' to generate the orphaned logins
>run sp_changedbowner 'sa'
>change the recovery plan of the UAT back to its original as the Prod may have a different recovery plan.
>send mail to use rto verify the database access for a couple of logins
That should be basic steps. I will soon update the script to get the access list for the users in a database.
Script for users and user access :
set nocount on
-- users created for logins and with their defualt schema
select 'CREATE USER ['+p.name +'] '+
case when l.name is not null then ' FOR LOGIN ['+l.name+'] ' else ' WITHOUT LOGIN ' end +
case when p.default_schema_name is not null then ' WITH DEFAULT_SCHEMA = '+p.default_schema_name +' 'else ' ' end + CHAR(13)+CHAR(10)+ 'GO '
from sys.syslogins l
right outer join sys.database_principals p on p.SID=l.SID
where (p.type='S' or p.type ='U')
--roles related to users in database
select 'Exec sp_addrolemember ' + ''''+prole.name+''''+','+'''' +puser.name +'''' + CHAR(13)+CHAR(10)+' GO'
from sys.database_role_members r
join sys.database_principals prole on r.role_principal_id= prole.principal_id
join sys.database_principals puser on r.member_principal_id= puser.principal_id
order by 1
set nocount off
-------------------------------------------------
Hope this helps.I am still missing the grants in the script. Will work on that today and add it to the script.
Tushar
