SQL Server DB Refresh

SQL Server DB Refresh



SQL Server DB Refresh from SQL Server 2008 to SQL Server 2008 - Article

Note:
For SQL Server 2005 to SQL Server 2008/2012, Post DB Refresh, Please change DB Compatibility level to latest version.

@ Production SQL Server Instance
****************************

@ Production Database:
*******************

STEP-1:   Perform the production database full backup with COPY_ONLY.

--TSQL SCRIPT:

BACKUP DATABASE ProdDB
TO DISK = 'E:\Source\MSSQL\BACKUPS\ProdDB_FULL_BKP_DEC262015_12.40PM.BAK'
WITH COPY_ONLY

STEP-2: Move this backup file to Development Server using the below command.

Go to RUN.
Type the command ----   \\DestinationServerName\E$\MSSQL\BACKUPS


@ Development SQL Server Instance
*******************************
--Perform full backup if required as per the application team confirmation
--Also check whether we have enough space in Backup Drive (example: E:\MSSQL\Backups)

STEP-3:   Perform the development database full backup normally.

--No need of copy_only option for development databases.

BACKUP DATABASE DevDB
TO DISK = 'E:\Destination\MSSQL\BACKUPS\DevDB_FULL_BKP_DEC262015_12.50PM.BAK'

STEP-4: Run the below script on Development to extract DB Users, Roles, Object Level Permissions.

--Extracting DB Users before db refresh at test/dev/stage.

--http://www.coresql.in/how_to_script_object_level_permission.html

--Extracting DB Users from the required Development Database.


SET nocount ON

SELECT scripts AS '--Scripts'
FROM   (SELECT Getdate() AS ScriptDateTime,
               'CREATE USER [' + DP.name + '] FOR LOGIN ['
               + SP.name + ']' + CASE WHEN DP.type_desc != 'WINDOWS_GROUP' THEN
' WITH DEFAULT_SCHEMA = ['+Isnull(DP.default_schema_name, 'dbo')+']'
--+ CHAR(13)+CHAR(10)+'GO'
ELSE ''--+ CHAR(13)+CHAR(10)+'GO'
END       AS Scripts
FROM   sys.database_principals DP,
sys.server_principals SP
WHERE  SP.sid = DP.sid
AND DP.name NOT IN ( 'DBO', 'GUEST', 'INFORMATION_SCHEMA', 'SYS',
                     'PUBLIC', 'DB_OWNER', 'DB_ACCESSADMIN',
                     'DB_SECURITYADMIN',
                     'DB_DDLADMIN', 'DB_BACKUPOPERATOR', 'DB_DATAREADER'
                     ,
                         'DB_DATAWRITER',
                     'DB_DENYDATAREADER', 'DB_DENYDATAWRITER', 'DB_X' )
UNION

--Extracting Database Roles Permissions for the DB USers.

SELECT Getdate() AS ScriptDateTime,
'EXEC sp_addrolemember @rolename ='
+ Space(1)
+ Quotename(User_name(rm.role_principal_id), '''')
+ ', @membername =' + Space(1)
+ Quotename(User_name(rm.member_principal_id), '''')
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Role Memberships'
FROM   sys.database_role_members AS rm
WHERE  User_name(rm.role_principal_id)
+ User_name(rm.member_principal_id) != 'DB_OWNERDBO'
--ORDER BY rm.role_principal_id ASC
UNION



--Extracting object level permissions

SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ 'ON ' + Quotename(User_name(obj.schema_id))
+ '.' + Quotename(obj.name) + CASE WHEN cl.column_id IS NULL THEN Space(
0
) ELSE
'(' + Quotename(cl.name) + ')' END + Space(1) + 'TO'
+ Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
                                                                    END
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Object Level Permissions'
FROM   sys.database_permissions AS perm
INNER JOIN sys.objects AS obj
        ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl
       ON cl.column_id = perm.minor_id
          AND cl.[object_id] = perm.major_id
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION


--Extracting database level permissions


SELECT Getdate() AS ScriptDateTime,
CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END +
Space
(1) +
perm.permission_name + Space(1)
+ Space(1) + 'TO' + Space(1)
+ Quotename(User_name(usr.principal_id)) COLLATE database_default + CASE
WHEN perm.state <> 'W' THEN Space(0)
ELSE Space(1) + 'WITH GRANT OPTION'
                                                                    END
          --+ CHAR(13)+CHAR(10)+'GO'
          AS '--Database Level Permissions'
FROM   sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
WHERE  perm.major_id = 0
AND ( permission_name
      + User_name(usr.principal_id) != 'CONNECTDBO' )
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
) AS UserScripts
ORDER  BY scripts


 
Save the OUTPUT of the above script on the Development Server as 'D:\MSSQL\DevDB_Objects.txt'


STEP-5:   Now restore the Production Database Backup file on top of Development DB as follows.

--Restore with replace.

RESTORE DATABASE DevDB
FROM  DISK = 'E:\Destination\MSSQL\BACKUPS\ProdDB_FULL_BKP_DEC262015_12.40PM.BAK'
WITH REPLACE,
Move 'DevDB' to 'E:\Destination\MSSQL\Data\DevDB_Data.mdf',
Move 'DevDB_Log' to 'O:\Destination\MSSQL\Log\DevDB_Log.ldf'

--Monitor the DB Restore Remaining Time Report:
--http://www.sanssql.com/2008/11/query-to-find-time-remaining-to.html

USE master
GO

SELECT
          Percent_Complete,
          Start_Time ,
          Command,
          b.Name AS DatabaseName, --Sometimes this will be "Main" as the database will not be accesiable.
          DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
          (estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
          INNER JOIN sys.databases b
          ON a.database_id = b.database_id
WHERE
          Command like '%Restore%'
          OR Command like '%Backup%'
          AND Estimated_Completion_Time > 0





@ Development Server:
STEP-6:   Delete the production db users which are now available in Development database as orphan users.
            

--Cross check for any orphan users using below script

USE DevDB
GO
SP_CHANGE_USERS_LOGIN 'REPORT'


STEP-7:  Use the  Script DevDB_Objects.txt taken on Dev DB and execute it on Dev DB "DevDB".

DevDB_Objects Script Path:   D:\MSSQL\DevDB_Objects.txt

Note: Again check for any orphan users, if any exists fix them.

STEP-8: Finally cross the DB Roles/permissions for the respective DB users/Logins on Development DB  --  DevDB. 

Comments

Post a Comment

Popular posts from this blog

CREATE VIEW SQL: Modifying views in SQL Server

Image
Introduction The primary goal will be to get familiar with the  ALTER VIEW  command used to modify views and change the output. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query. Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of the CREATE reserved keyword ALTER is used. Creating view As I mentioned earlier, let’s use the code from below to create a bit more complex view: 1 2 3 4 5 6 7 8 9 10 11 CREATE VIEW vTop3SalesByQuantity AS      SELECT TOP 3 --will only return first 3 records from query      Sales . ProductID ,      Name AS ProductName ,  ...
Read more

Transfer logins and passwords between instances of SQL Server

  Transfer logins and passwords between instances of SQL Server This article describes how to transfer the logins and the passwords between different instances of SQL Server running on Windows. Original product version:    SQL Server Original KB number:    918992 Introduction This article describes how to transfer the logins and passwords between different instances of Microsoft SQL Server.  Note The instances may be on the same server or on different servers, and their versions may differ. More information In this article, server A and server B are different servers. After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, users may be unable to log in to the database on server B. Additionally, users may receive the following error message: Login failed for user ' MyUser '. (Microsoft SQL Server, Error: 18456) This problem occurs because you did not transfer the logins and the passwords from the insta...
Read more

SQL SERVER –Different Ways to Find Recovery Model for Database

Image
  This is a very easy method and it gives all the database information in one script. SELECT  name  AS  [Database Name] , recovery_model_desc  AS  [Recovery Model]  FROM  sys.databases GO Method 4 This method provides only one database at a time. SELECT  'ADVENTUREWORKS'  AS  [Database Name] , DATABASEPROPERTYEX ( 'ADVENTUREWORKS' ,  'RECOVERY' ) AS  [Recovery Model] GO My recommendation
Read more