Powershell DB Restore

# PSHELL 3
$ErrorActionPreference = "Stop"

#SDK version config
$sdkversion = 100
$sdkpath = "D:\Folding Space\SDK\SDK\Assemblies\";

#load SMO types
Add-Type -Path "$sdkpath\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "$sdkpath\Microsoft.SqlServer.SmoExtended.dll"

$today = Get-Date
$datepart = $today.Year.ToString() + "_" + $today.Month.ToString("00") + "_" + $today.Day.ToString("00");

$path1 = "\\server-sql02\g$\SQLBackups\db1*" + $datepart + "*.bak"
$path2 = "\\server-sql03\G$\SQLBackup\db2*" + $datepart + "*.bak"
$path3 = "\\server-sql03\G$\SQLBackup\db3*" + $datepart + "*.bak"

write "Emptying Backup Directory"
Remove-Item –path D:\Backups\* -include *.bak

write "Copying Backup Files to Backup Directory"
Copy-Item \\server-sql01\e$\newSQLBackupLocation\iprnt*.bak D:\Backups\db4.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db5*.bak D:\Backups\IPSA_MSCRM.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db6*.bak D:\Backups\JANE_IPSA.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db7*.bak D:\Backups\JANE_MEMBERS.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db8*.bak D:\Backups\JANE_STAFF.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db9*.bak D:\Backups\DYNAMICS.bak

Copy-Item $path1 D:\Backups\IPSA-MASTER.bak
Copy-Item $path2 D:\Backups\IRIS_MSCRM.bak
Copy-Item $path3 D:\Backups\IPSAGateway.bak

$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Login = "username"
$srv.ConnectionContext.Password = "password"
$srv.ConnectionContext.ServerInstance = "db server"

##databases
$target = $srv.ConnectionContext

#$tx = $target.Parent.ConnectionContext.BeginTransaction()
$sql = (gc '.\DB Restore.sql' -Raw)   
write "Restoring databases from backups"
$target.ExecuteNonQuery($sql)
#$target.Parent.ConnectionContext.CommitTransaction()

write "Generating new output for data migration"
$process = New-Object System.Diagnostics.Process
$process.StartInfo.FileName = "D:\DataMigrator.exe";
$process.StartInfo.UseShellExecute = $false
$process.Start()
if ($process.Start())
{
    $output = $process.StandardOutput.ReadToEnd()
}

SQL Restore Script:

ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db5 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db7 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db8 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db9  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE db1 FROM DISK = 'D:\Backups\db1.bak' WITH REPLACE;  

RESTORE DATABASE db2 FROM DISK = 'D:\Backups\db2.bak' WITH REPLACE;  

RESTORE DATABASE db3 FROM DISK = 'D:\Backups\db3.bak' WITH REPLACE;  

RESTORE DATABASE db4 FROM DISK = 'D:\Backups\db4.bak' WITH REPLACE;  

RESTORE DATABASE db5 FROM DISK = 'D:\Backups\db5.bak' WITH REPLACE;  

RESTORE DATABASE db6 FROM DISK = 'D:\Backups\db6.bak' WITH REPLACE;  

RESTORE DATABASE db7 FROM DISK = 'D:\Backups\db7.bak' WITH REPLACE;  

RESTORE DATABASE db8 FROM DISK = 'D:\Backups\db8.bak' WITH REPLACE;  

RESTORE DATABASE db9 FROM DISK = 'D:\Backups\db9.bak' WITH REPLACE;