--- 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!! ***************************************
Comments
Post a Comment