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

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 ,      SUM ( Sales . Quantity ) AS TotalQuantity      FROM Sales            J
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

Syntaxes for Restore

 restore filelistonly from disk = 'E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak ' select * from sys.sysaltfiles restore database [AdventureWorks2012]  from disk = 'E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak'  with replace , recovery ; restore database [RAVIDBA]  from disk ='E:\RAVIDBA\FULL\AdventureWorks2012_full_backup_20210529_120800.bak' with move 'AdventureWorks2012'  to 'D:\MYSQLSERVER\MSSQL11.RAVISQLSERVERDBA\MSSQL\DATA\RAVIDBA.mdf',  move 'AdventureWorks2012_log'  to 'D:\MYSQLSERVER\MSSQL11.RAVISQLSERVERDBA\MSSQL\LogDB\RAVIDBA_log.ldf' , replace, recovery , stats = 5 ;
Read more