DB refresh from production database to test database using scripts-while many blocks occurs (sp_who2)

DB refresh from production database to test database using scripts-while many blocks occurs (sp_who2)
1->first run role permission scripts (check google) to get roles & permissions from Test database before database refreshes & kept for future run after test database refresh.

2->take create user script for specific test database and kept for future run after database refresh.

3-> select * from sysfiles to find mdf & ldf file path & compare with another databases in that server to match.

4-> SELECT DATABASEPROPERTYEX(‘dbanme’, 'Recovery') to find recovery model and to retain as it was.

5-> exec sp_change_users_login 'report' to get orphan users.
  Make a note of it.

6.>if blocking occurs during database refresh means-->follow step 7

7.>use master
    go
    alter database testdbname
    set single_user
    go
    Restore database testdatabasename
    from disk ='' with recovery
    go
    alter database testdbname
    set multi_user


8.>Now Blocking gone and restoring test database fine

9. Repeat steps 3, 4, &5 to verify


Let me know any other method to avoid blocking during database referesh?
 

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)