PowerShell Script to copy from PROD server to Dev03 and from Dev03(AG Primary) to Dev04(AG secondary) server.
You can schedule a job in task scheduler to automatic copy file.
# Delete all existing backup from below location
Remove-Item D:\ProdBackup\*.bak
Write-Output "Deleted all old existing backup from Dev03 server : TASK COMPLETED"
# Source and Destination backup file location
$_sourcePath ="\\ABC-PROD01\e$\CopyOnlyBackup"
$_destinationPath = "D:\ProdBackup";
# Pick latest .bak file and Copying file Des to Source and rename
$Datetime =Get-Date
Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )
@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force}
$Datetime1 =Get-Date
Write-Output ("Latest backup file copy Completed on Dev03 server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )
################# Copy backup From Dev03 to Dev04 ######################
# Delete all existing backup from below location
Remove-Item \\ABC-DEV04\d$\ProdBackup\*.bak
Write-Output "Deleted all old existing backup from Dev04 server : TASK COMPLETED"
# Source and Destination backup file location
$_sourcePath ="D:\ProdBackup"
$_destinationPath = "\\ABC-DEV04\d$\ProdBackup";
# Pick latest .bak file and Copying file Des to Source and rename
$Datetime =Get-Date
Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )
@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force}
$Datetime1 =Get-Date
Write-Output ("Latest backup file copy Completed on Dev server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )
Once Copy done by above Script.
Create below job on AG primary server Dev03.
Note: This will contain total 6 step to Restore DB on Primary and Secondary server and will configure AG between Dev03 and and Dev04 automatically.
USE [msdb]
GO
/****** Object: Job [Monthly_Test_DB_Refresh_From_PROD] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC msdb.dbo.sp_delete_job @job_id=N'8c70a241-323d-48e5-80ce-f97190c07661', @delete_unused_schedule=1
GO
/****** Object: Job [Monthly_Test_DB_Refresh_From_PROD] Script Date: 7/6/2021 7:45:32 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 7/6/2021 7:45:32 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monthly_Test_DB_Refresh_From_PROD',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1-Remove AG from Dev Primary] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1-Remove AG from Dev Primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RemoveDBFromAGgroup.sql
',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [2-DeleteDBOnSecondary] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2-DeleteDBOnSecondary',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=3,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV04 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnSecondary.sql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [3-DeleteDBOnPrimary] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3-DeleteDBOnPrimary',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=4,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnPrimary.sql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [4-RestoreDBOnPrimary] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'4-RestoreDBOnPrimary',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=5,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnPrimary.sql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [5-RestoreDBOnSecondary] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'5-RestoreDBOnSecondary',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=6,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnSecondary.sql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [6- Add DB in AG] Script Date: 7/6/2021 7:45:32 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'6- Add DB in AG',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\AddDBInAG.sql',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Now the below SQL script of code divided in 6 step for your above created SQL Job.
Save each steps of code on Dev03(AG primary server) at D:\ProdBackup\AG_Refresh_SQL_Scripts\*.sql format
Note: File name refer from created job steps.
All 6 *.sql file will access by above job.
Note: Before/while saving the .sql file go to Query --> and SQLCMD option.
-----------File name 1 -Remove DB from AG group-------------------------
:connect ABC-DEV03
USE [master]
GO
/****** Object: AvailabilityDatabase testdb Script Date: 7/1/2021 2:45:39 AM ******/
ALTER AVAILABILITY GROUP [ABC-DV-AWO]
REMOVE DATABASE testdb;
GO
-----------File name 2 Delete DB on Secondary ------------------------------
:connect ABC-DEV04
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '
GO
USE [master]
GO
/****** Object: Database testdb Script Date: 7/1/2021 2:48:58 AM ******/
DROP DATABASE testdb
GO
----------File name 3 Delete DB on Primary------------------------------
:connect ABC-DEV03
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '
GO
use testdb
GO
use [master]
GO
USE [master]
GO
ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
GO
USE [master]
GO
/****** Object: Database testdb Script Date: 7/1/2021 2:51:08 AM ******/
DROP DATABASE testdb
GO
-------File name 4 Restore DB on Secondary -------------------------------
:connect ABC-DEV04
RESTORE DATABASE TestDB
FROM DISK = N'D:\ProdBackup\testdb.bak'
WITH FILE = 1,
MOVE N'testdb' TO N'E:\Data\testdb.mdf',
MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',
NORECOVERY,
NOUNLOAD, REPLACE, STATS = 5
-------File name 5 Restore DB on Primary-------------------------------
:connect ABC-DEV03
RESTORE DATABASE TestDB
FROM DISK = N'D:\ProdBackup\testdb.bak'
WITH FILE = 1,
MOVE N'testdb' TO N'E:\Data\testdb.mdf',
MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',
NOUNLOAD, REPLACE, STATS = 50
---------------File name 6 - add DB in AG -----------------------
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect ABC-DEV03
USE [master]
GO
ALTER AVAILABILITY GROUP [ABC-DV-AWO]
MODIFY REPLICA ON N'ABC-DEV04' WITH (SEEDING_MODE = MANUAL)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [ABC-DV-AWO]
ADD DATABASE testdb;
GO
:Connect ABC-DEV04
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes
if (serverproperty('IsHadrEnabled') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'ABC-DV-AWO'
select @replica_id = replicas.replica_id from
master.sys.availability_replicas as replicas
where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from
master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay '00:00:10'
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE testdb SET HADR AVAILABILITY GROUP = [ABC-DV-AWO];
GO
GO
