Powershell database restore proc creation

Powershell to create basic SQL Server database restore proc for dev DB automation.  Script was designed to run in an Azure Devops pipeline directly on the SQL Server (via deployment agent)

 Import-Module sqlserver  
 $ServerName = hostname  
   
 # if procs don't already exist, create empty placeholders to allow "alter" statement to work later  
 $sqlCreateProcs=@"  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
   
 if OBJECT_ID('CompactLogs') IS NULL  
   EXEC ('CREATE PROCEDURE [dbo].[CompactLogs] as')  
   GO  
   
 if OBJECT_ID('KillSessions') IS NULL  
   EXEC('CREATE PROCEDURE [dbo].[KillSessions] as')    
   GO  
   
 if OBJECT_ID('RestoreDatabase') IS NULL  
   EXEC('CREATE PROCEDURE [dbo].[RestoreDatabase] as')  
   GO  
   
 "@  
   
 $sqlRestoreDBProc=@"  
 USE [master]  
 GO  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
   
 --   @DatabaseName  
 --   @filePrefix  
 --   @dataStamp - YYYYMMDD  
 --      @targetDrive - physical drive  
   
   
 ALTER PROCEDURE [dbo].[RestoreDatabase]  
    @DatabaseName varchar(100),  
    @filePrefix varchar(100),  
    @dataStamp varchar(100),  
    @targetDrive varchar(3),  
    @backupPath varchar(100)  
 AS  
 BEGIN  
    SET NOCOUNT ON;  
   
    declare @restoreStatement varchar(2048)  
    DECLARE @shrinkStatement varchar(2048)  
           
           
   if exists(select * from sys.databases where name = @DatabaseName)  
   
       begin  
   
          exec master.dbo.Inf_KillSessions @DatabaseName  
   
          DECLARE @singleUserStatement varchar(255)  
          SELECT @singleUserStatement = 'ALTER DATABASE '+@DatabaseName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'  
          EXEC (@singleUserStatement)  
   
          select @restoreStatement = 'RESTORE DATABASE '+@DatabaseName+' FROM DISK = N'''+@backupPath+'\'+@filePrefix+'-'+@dataStamp+'.BAK'' WITH FILE = 1, MOVE N''RiskEngine_Data'' TO N'''+@targetDrive+':\Databases\'+@DatabaseName+@dataStamp+'.MDF'', MOVE N''RiskEngine_Log'' TO N'''+@targetDrive+':\Databases\'+@DatabaseName+@dataStamp+'.LDF'', NOUNLOAD, REPLACE, STATS = 1'  
          EXEC (@restoreStatement)  
   
          if exists(select * from sys.databases where name = @DatabaseName)  
          begin  
             DECLARE @multiUserStatement varchar(255)  
             SELECT @multiUserStatement = 'ALTER DATABASE '+@DatabaseName+' SET MULTI_USER'  
             EXEC (@multiUserStatement)  
          end  
   
          exec master.dbo.Inf_CompactLogs @DatabaseName  
            
          -- future version of script could fix orphaned logins  
          -- EXEC sp_change_users_login ''Auto_Fix'', ''MyLogin'''  
            
          -- future version of script could enable CLR procs  
          --EXEC sp_configure 'clr enabled' ,1;  
            
       end  
   else  
       begin  
         
          select @restoreStatement = 'RESTORE DATABASE '+@DatabaseName+' FROM DISK = N'''+@backupPath+'\'+@filePrefix+'-'+@dataStamp+'.BAK'' WITH FILE = 1, MOVE N''RiskEngine_Data'' TO N'''+@targetDrive+':\Databases\'+@DatabaseName+@dataStamp+'.MDF'', MOVE N''RiskEngine_Log'' TO N'''+@targetDrive+':\Databases\'+@DatabaseName+@dataStamp+'.LDF'', NOUNLOAD, REPLACE, STATS = 1'  
          EXEC (@restoreStatement)  
         
          exec master.dbo.Inf_CompactLogs @DatabaseName  
         
       end  
   
 END  
 "@  
   
 $sqlKillSessionsProc=@"  
 USE [master]  
 GO  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 ALTER PROCEDURE [dbo].[KillSessions]  
    @DatabaseName varchar(100)  
 AS  
 BEGIN  
       DECLARE @sessions TABLE([spid] FLOAT,[ecid] FLOAT,[status] VARCHAR(1000),[loginame] VARCHAR(1000),[hostname] VARCHAR(1000),[blk] FLOAT,[dbname] VARCHAR(1000),[cmd] VARCHAR(1000),[request_id] FLOAT)  
       INSERT INTO @sessions EXEC sp_who  
       DECLARE @spid int  
       DECLARE db_cursor CURSOR FOR SELECT spid FROM @sessions WHERE dbname = @DatabaseName and spid <> @@SPID and spid > 50  
       OPEN db_cursor FETCH NEXT FROM db_cursor INTO @spid   
       WHILE @@FETCH_STATUS = 0   
       BEGIN   
          DECLARE @killStatement varchar(100)  
          SELECT @killStatement = 'kill '+CONVERT(VARCHAR, @spid)  
          EXEC (@killStatement)  
          FETCH NEXT FROM db_cursor INTO @spid   
       END   
       CLOSE db_cursor   
       DEALLOCATE db_cursor  
 END  
 "@  
   
 $sqlCompactLogsProc=@"  
 USE [master]  
 GO  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 ALTER PROCEDURE [dbo].[CompactLogs]  
    @DatabaseName varchar(100)  
 AS  
 BEGIN  
   
    if not exists(select * from sys.databases where name = @DatabaseName)  
       print 'Database not found : '+@DatabaseName  
    else  
    begin  
       exec master.dbo.KillSessions @DatabaseName  
       if exists(select * from sys.databases where name = @DatabaseName)  
       begin  
          DECLARE @shrinkStatement varchar(2048)  
          SELECT @shrinkStatement = 'ALTER DATABASE '+@DatabaseName+' SET RECOVERY SIMPLE; USE '+@DatabaseName+'; DBCC SHRINKFILE (RiskEngine_Log, 64);'  
          EXEC (@shrinkStatement)  
       end  
    end  
 END  
 "@  
   
   
 Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlCreateProcs  
 Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlKillSessionsProc  
 Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlCompactLogsProc  
 Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlRestoreDBProc