$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCatalog = "master";
$SqlLogin = "sa";
$arrayofDATA=@(
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\analit.sql'
SERVER="."
Pass="123456"
}),
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\kassa.sql'
SERVER=".\KASSA"
Pass="654321"
}))
 
foreach($data in $arrayofDATA)
{
    $SERV=$data.Server
    $PATH=$data.PATH
    $Pass=$data.Pass
    $SqlConnection.ConnectionString = "Server=$SERV; Database=$SqlCatalog; User ID=$SqlLogin; Password=$Pass;"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandText = gc ($PATH)
    $objReader = $SqlCmd.ExecuteReader()
    while ($objReader.read()) {
    echo $objReader.GetValue(0)
    }
    $objReader.close()
    $SqlConnection.Close()
}

DECLARE @Path varchar(255), @Comand VARCHAR(8000), @DBtoRestore nVARCHAR(50), @sql VARCHAR(8000),  @str VARCHAR(8000) --@DBtoRestore NVARCHAR(4000)
select @Path  = 'Z:\Etalon_backups\'
if OBJECT_ID('tempdb..#dir') IS NOT NULL DROP TABLE #dir
create table #dir (f_name varchar(256))
set @Comand = 'dir ' + @Path + '*.bak'
insert into #dir exec master..xp_cmdshell @Comand
delete from #dir where f_name NOT LIKE '%.bak' OR f_name IS null
if OBJECT_ID('tempdb..#DBs') IS NOT NULL DROP TABLE #DBs
CREATE TABLE #DBs (DBName VARCHAR(50))
INSERT INTO #DBs
SELECT REVERSE(SUBSTRING(reverse(f_name), 1, CHARINDEX(' ',reverse(f_name))-1 )) FROM #dir
--CREATE DATABASE OtSigns
USE master 
IF NOT EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'OtSigns')
CREATE DATABASE OtSigns
ALTER DATABASE OtSigns SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OtSigns SET MULTI_USER
SELECT @DBtoRestore = DBName FROM #DBs WHERE DBName LIKE 'OtSigns%'
EXEC(
'RESTORE DATABASE OtSigns 
FROM  DISK = ''Z:\Etalon_backups\'+@DBtoRestore+'''
WITH  MOVE ''OtSigns'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OtSigns.mdf'',
MOVE ''OtSigns_log'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OtSigns_log.ldf'', REPLACE'
)
ALTER DATABASE OtSigns SET RECOVERY FULL 
IF NOT EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'OTData')
CREATE DATABASE OTData
ALTER DATABASE OTData SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OTData SET MULTI_USER
SELECT @DBtoRestore = DBName FROM #DBs WHERE DBName LIKE 'OTData%'
EXEC(
'RESTORE DATABASE OTData 
FROM  DISK = ''Z:\Etalon_backups\'+@DBtoRestore+'''
WITH  MOVE ''OTData_D'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OTData.mdf'',
MOVE ''OTData_L'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OTData_log.ldf'', REPLACE'
)
ALTER DATABASE OTData SET RECOVERY FULL
IF NOT EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'OTUtils')
CREATE DATABASE OTUtils
ALTER DATABASE OTUtils SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OTUtils SET MULTI_USER
SELECT @DBtoRestore = DBName FROM #DBs WHERE DBName LIKE 'OTUtils%'
EXEC(
'RESTORE DATABASE OTUtils 
FROM  DISK = ''Z:\Etalon_backups\'+@DBtoRestore+'''
WITH  MOVE ''OTUtils_Data'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OTUtils.mdf'',
MOVE ''OTUtils_Log'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\OTUtils_log.ldf'', REPLACE'
)
ALTER DATABASE OTUtils SET RECOVERY FULL
IF NOT EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'Security')
CREATE DATABASE Security
ALTER DATABASE Security SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Security SET MULTI_USER
SELECT @DBtoRestore = DBName FROM #DBs WHERE DBName LIKE 'Security%'
EXEC(
'RESTORE DATABASE Security 
FROM  DISK = ''Z:\Etalon_backups\'+@DBtoRestore+'''
WITH  MOVE ''Security_Data'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\Security.mdf'',
MOVE ''Security_Log'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\Security_log.ldf'''
)
ALTER DATABASE Security SET RECOVERY FULL
IF NOT EXISTS (SELECT 1 FROM sysdatabases WHERE name = 'UserLog')
CREATE DATABASE UserLog
ALTER DATABASE UserLog SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE UserLog SET MULTI_USER
SELECT @DBtoRestore = DBName FROM #DBs WHERE DBName LIKE 'UserLog%'
EXEC(
'RESTORE DATABASE UserLog 
FROM  DISK = ''Z:\Etalon_backups\'+@DBtoRestore+'''
WITH  MOVE ''UserLog'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\UserLog.mdf'',
MOVE ''UserLog_Log'' TO ''D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\UserLog_log.ldf'''
)
ALTER DATABASE UserLog SET RECOVERY FULL

<#
  sqlps dependency
  If module sqlps does not exist, install from:
    Microsoft SQL Server 2016 Feature Pack (https://www.microsoft.com/en-us/download/details.aspx?id=52676)
    - SQLSysClrTypes.msi
    - SharedManagementObjects.msi
    - PowershellTools.msi
#>

param(
  [Parameter(Mandatory=$true,Position=0)]
  [string]$servername,
  [Parameter(Mandatory=$true,Position=1)]
  [string]$dbname,
  [Parameter(Mandatory=$true,Position=2)]
  [string]$filename)



Push-Location
Import-Module "sqlps" -DisableNameChecking
Pop-Location

<#
  MAIN
#>

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $filename, "FILE"
$restore.Devices.Add($device)
try {
  $filelist = $restore.ReadFileList($server)
}
catch {
	$exception = $_.Exception
	Write-Host "$exception. `n`nDoes the SQL Server service account have acccess to the backup location?" -ForegroundColor Red
  exit 1
}

$filestructure = @{}; $datastructure = @{}; $logstructure = @{}
$logfiles = $filelist | Where-Object {$_.Type -eq "L"}
$datafiles = $filelist | Where-Object {$_.Type -ne "L"}

# Data Files (if db has filestreams, make sure server has them enabled)
$defaultdata = $server.DefaultFile
$defaultlog = $server.DefaultLog
if ($defaultdata.Length -eq 0) {
    $defaultdata = $server.Information.MasterDBPath
}

if ($defaultlog.Length -eq 0) {
    $defaultlog = $server.Information.MasterDBLogPath
}

foreach ($file in $datafiles) {
	$newfilename = Split-Path $($file.PhysicalName) -leaf

	$datastructure.physical = "$defaultdata$newfilename"
	$datastructure.logical = $file.LogicalName
	$filestructure.add($file.LogicalName,$datastructure)
}

# Log Files
foreach ($file in $logfiles) {
	$newfilename = Split-Path $($file.PhysicalName) -leaf

	$logstructure.physical = "$defaultlog$newfilename"
	$logstructure.logical = $file.LogicalName
	$filestructure.add($file.LogicalName,$logstructure)
}

# Make sure big restores don't timeout
$server.ConnectionContext.StatementTimeout = 0

foreach	($file in $filestructure.values) {
	$movefile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
	$movefile.LogicalFileName = $file.logical
	$movefile.PhysicalFileName = $file.physical
	$null = $restore.RelocateFiles.Add($movefile)
}

Write-Host "Restoring $dbname to $servername" -ForegroundColor Yellow

# kill all connections
$server.KillAllProcesses($dbname)

try {

	$percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
		Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
	}
	$restore.add_PercentComplete($percent)
	$restore.PercentCompleteNotification = 1
	$restore.add_Complete($complete)
	$restore.ReplaceDatabase = $true
	$restore.Database = $dbname
	$restore.Action = "Database"
	$restore.NoRecovery = $false

  # take most recent backup set if there are more than one
  $restore.FileNumber = $restore.ReadBackupHeader($server).Rows.Count

	Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
	$restore.sqlrestore($servername)
	Write-Progress -id 1 -activity "Restoring $dbname to $servername" -status "Complete" -Completed

	Write-Host "Restore complete!" -ForegroundColor Green

  exit 0
}
catch {
	$exception = $_.Exception.InnerException
  Write-Host $exception -ForegroundColor Red
  exit 1
}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCatalog = "master";
$SqlLogin = "sa";
$arrayofDATA=@(
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\analit.sql'
SERVER="."
Pass="321"
}),
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\kassa.sql'
SERVER=".\KASSA"
Pass="123"
}))

foreach($data in $arrayofDATA)
{
    $SERV=$data.Server
    $PATH=$data.PATH
    $Pass=$data.Pass
    $SqlConnection.ConnectionString = "Server=$SERV; Database=$SqlCatalog; User ID=$SqlLogin; Password=$Pass;"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandTimeout = 0;
    $SqlCmd.CommandText = [IO.File]::ReadAllText($PATH) ;
    $objReader = $SqlCmd.ExecuteReader()
    while ($objReader.HasRows)
{

    while ($objReader.read()) {
    echo $objReader.GetValue(0)
}
$objReader.NextResult();    
}
    $objReader.close()
    $SqlConnection.Close()
}