#SQLserver #Database Refresh #DBA #Backup #Restoration #Recovery #Backup Validation #Restoration Validation #OrphanUser #Orphanuser Fix
#ProductionDBA #BasicTask #T-SQL #Scripts #BackupValidation Script (Highly Recommended), #Restoration Validation Script

Hi Guys!!

Hope all r Safe & dng Gr8 🙂

Myself Dev, PRODUCTION SQL Server DBA having 7+ yrs of Experience.
I have started my Youtube Channel to Share my SQL Server DBA knowledge as much as possible, and will try to make a quality video for my viewers, so would require your help as well to correct me if I’m lagging anywhere.
I always loved my DBA Profession, and I would like to upload my Daily Tasks, Disaster Recovery, HA-DR, Brain-Storming Sessions etc… Stay tuned guys! will upload with loads of topics soon.
So, Please subscribe to my Channel 🙂
Thank you Guys!!

Video Notes Description:

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 DBARefreshPROD_BackupPROD_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 DBARefreshPROD_BackupPROD_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 DBARefreshDEV_BackupDEV_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 DBARefreshDEV_BackupDEV_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 DBARefreshPROD_BackupPROD_Full_Backup_10_10_2020.bak’ WITH FILE = 1,
MOVE N’PROD’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL15.DEVMSSQLDATADEV.mdf’,
MOVE N’PROD_log’ TO N’C:Program FilesMicrosoft SQL ServerMSSQL15.DEVMSSQLDATADEV_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!! ***************************************