SQL - Powershell Restore of a database

PowerShell Help
1

This is my script. I am trying to restore a sql backup over to another database. When i run this. This tried to override the original database even though i am specifying a new database. See my script below:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

$File =gci c:\sqlbackups -include cstest.bak -Recurse
$CrockfordFile = gci c:\sqlbackups -include cstest.bak -Recurse

copy-Item $file -Destination C:\Webpub\Crockford

Import-Module SQLPS -DisableNameChecking
$svr = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “SUPPORTPC14\SQLEXPRESS”;
{
$svr.KillAllProcesses(“cstest”)
$svr.KillDatabase(“cstest”)
}

Restore-SqlDatabase -Serverinstance “SUPPORTPC14\SQLEXPRESS” -database “CSTest” -BackupFile $CrockfordFile -ReplaceDatabase

2

Yeah, SMO can be a bit tricky. I’ll tell you - you may get a better answer someplace like StackOverflow, because you’re really using SMO, which is part of .NET, and not really using “native” PowerShell stuff, which is what we specialize in here.

I do know that there’s a complex override needed in order to keep a backup from going back to its original location, but I’ve never needed to code for that myself.