DB Refresh from Production Database(PROD) to QA/DEV Database (DEV) on a Server ...

  Please do a Refresh from Production Database(PROD) to QA/DEV Database (DEV) on a Server ...

--- Steps in Line 1. Take a Full Backup(.bak) of Source Database. --Completed 2. Validate the taken Backup. -- Completed 3. Take the Full Backup(.bak) of Destination Database. --Completed 4. Validate the taken Backup. -- Completed 5. Restore the Source Database (.bak) file to the destination Database. -- Completed 6. Valid the Restoration. -- Completed 7. Fix Orphan User. --Completed --- Step wise ---1. Take a Backup of a PROD Database --(Source) BACKUP DATABASE [PROD] TO DISK = N'C:\Tech DBA\Refresh\PROD_Backup\PROD_Full_Backup_10_10_2020.bak' WITH NOFORMAT, NOINIT, NAME = N'PROD-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO ---2. Validate Backup declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'PROD' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'PROD' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''PROD'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\Tech DBA\Refresh\PROD_Backup\PROD_Full_Backup_10_10_2020.bak' WITH FILE = @backupSetId GO ---3. Take a Backup of the DEV Database --(Destination) BACKUP DATABASE [DEV] TO DISK = N'C:\Tech DBA\Refresh\DEV_Backup\DEV_Full_Backup_10_10_2020.bak' WITH NOFORMAT, NOINIT, NAME = N'DEV-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO ---4. Validate Backup declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'DEV' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DEV' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DEV'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'C:\Tech DBA\Refresh\DEV_Backup\DEV_Full_Backup_10_10_2020.bak' WITH FILE = @backupSetId GO ---5. Restore from SOurce backup File to Destination Database USE [master] RESTORE DATABASE [DEV] FROM DISK = N'C:\Tech DBA\Refresh\PROD_Backup\PROD_Full_Backup_10_10_2020.bak' WITH FILE = 1, MOVE N'PROD' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.DEV\MSSQL\DATA\DEV.mdf', MOVE N'PROD_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.DEV\MSSQL\DATA\DEV_log.ldf', NOUNLOAD, REPLACE, STATS = 5 GO ---6. Validate the Restoration WITH MostRecentRestore AS ( SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY RH.Destination_database_name ORDER BY RH.Restore_Date DESC), RH.Restore_date, BS.[database_name] as Source_Database, RH.Destination_Database_Name, BS.Backup_Start_Date, BS.Backup_Finish_Date, CASE WHEN RH.restore_type = 'D' THEN 'Database' WHEN RH.restore_type = 'F' THEN 'File' WHEN RH.restore_type = 'G' THEN 'Filegroup' WHEN RH.restore_type = 'I' THEN 'Differential' WHEN RH.restore_type = 'L' THEN 'Log' WHEN RH.restore_type = 'V' THEN 'Verifyonly' WHEN RH.restore_type = 'R' THEN 'Revert' ELSE RH.restore_type END AS Restore_Type, RH.[Replace], RH.[Recovery], RH.Restore_Date AS Restored_On, BMF.physical_device_name AS Restored_From, RF.destination_phys_name AS Current_DB_File_Location, RH.user_name AS Restored_By, BS.machine_name, BS.Server_Name FROM msdb.dbo.RestoreHistory RH INNER JOIN msdb.dbo.BackupSet BS ON RH.backup_set_id = BS.backup_set_id INNER JOIN msdb.dbo.restorefile RF ON RH.Restore_History_id = RF.Restore_History_id INNER JOIN msdb.dbo.Backupmediafamily BMF ON bs.media_set_id = bmf.media_set_id ) SELECT top 5 * FROM MostRecentRestore WHERE destination_database_name = 'DEV' ---7. a) EXEC sp_change_users_login 'REPORT' b) sp_change_users_login AUTO_FIX, ‘UserName’, NULL, ‘Password’ *****************Yes! You DID It Successfully!! ***************************************
SHOW LESS

Comments

Popular posts from this blog

CREATE VIEW SQL: Modifying views in SQL Server

Transfer logins and passwords between instances of SQL Server

SQL SERVER –Different Ways to Find Recovery Model for Database