Fresh Backup Copy

--Create below table on your database server.

USE [DBATasks]
GO
IF OBJECT_ID(N'RefreshDBs', N'U') IS NULL
BEGIN  
CREATE TABLE [dbo].[RefreshDBs](
[SourceServer] [sysname] NOT NULL,
[DBName] [sysname] NOT NULL,
[FreshBackup] [char](5) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[RefreshDBs] ([SourceServer], [DBName], [FreshBackup]) VALUES (N'SourceServer\Instance', N'DatabaseName', N'Yes')
GO

--Schedule below code as SQL Agent Job on same database server where above table exists 

$databases = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "SELECT SourceServer, DBName, FreshBackup from DBATasks..RefreshDBs;"

$databases

foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
$Inst = $database.SourceServer #instance from the select query
$DBname = $database.DBName #databasename from the select query
$FBackup = $database.FreshBackup

$Fbackup

if ( $FBackup -match 'Yes')
{
$Fbackup
sqlcmd -E  -S $Inst -d DBATasks -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = $DBName, @Directory = NULL, @BackupType = 'FULL', @Compress = 'Y', @Verify = 'Y', @CleanupTime = 23, @CheckSum = 'Y', @LogToTable = 'Y'"
}

$Inst
$BackupLoation =  invoke-sqlcmd -ServerInstance $Inst  -Database msdb -Query "
DECLARE @BPath varchar(900);
SELECT TOP 1 @BPath = physical_device_name
FROM msdb.dbo.backupset b
     JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
       WHERE database_name like '$DBName'

             AND b.type = 'D'
       ORDER BY backup_finish_date DESC;

IF @BPath LIKE '_:\%'
SET @BPath = '\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@BPath, ':', '$');
ELSE IF @BPath like '\\%'
SET @BPath = @BPath
SELECT LEFT(@BPath, LEN(@BPath)-CHARINDEX('\', REVERSE(@BPath))) BackupFolder,
       REVERSE(LEFT(REVERSE(@BPath), CHARINDEX('\', REVERSE(@BPath))-1)) BackupFileName;
"

$CopyFolder = invoke-sqlcmd -ServerInstance $(ESCAPE_SQUOTE(SRVR)) -Database msdb -Query "DECLARE @DefaultBackupDirectory VARCHAR(200);
DECLARE @DBBackup VARCHAR(300);
DECLARE @DirTree TABLE
       (
       subdirectory NVARCHAR(255),
       depth        INT
       );
EXECUTE master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
SET @DBBackup = @DefaultBackupDirectory;
IF @DBBackup LIKE '_:\%'
SET @DBBackup = '\\'+CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+'\'+REPLACE(@DBBackup, ':', '$');
ELSE IF @DBBackup like '\\%'
SET @DBBackup = @DBBackup
SELECT @DBBackup DBBackupFile;"
$BackupLoation.BackupFolder
$BackupLoation.BackupFileName
$CopyFolder.DBBackupFile
#copy-item -Path "$BackupLocation.BackupPath" -Destination "$CopyFolder.DBBackupFile"
Robocopy $BackupLoation.BackupFolder $CopyFolder.DBBackupFile $BackupLoation.BackupFileName
}