Powershell restores SQL Server database to new database

Question

I have a database $CurrentDBand I want to restore the backup $CurrentDBto $NewDB. The T-SQL command looks like this:

USE [master]

ALTER DATABASE [NewDB] 
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [NewDB] 
FROM DISK = N'D:\Backups\CurrentDB.bak' 
WITH FILE = 1,  
     MOVE N'CurrentDB' TO N'D:\Databases\NewDB.mdf',  
     MOVE N'CurrentDB_log' TO N'D:\Logs\NewDB_log.ldf',  
     NOUNLOAD, REPLACE, STATS = 5

ALTER DATABASE [NewDB] 
    SET MULTI_USER
GO

I am trying to use a user Restore-SqlDatabase, but I do not know how to use it correctly-RelocateFile

$CurrentDB = "CurrentDB"
$NewDB = "NewDB"
$NewDBmdf = "NewDB.mdf"
$CurrentDBlog = "CurrentDB_log"
$NewDBldf = "NewDB_log.ldf"
$backupfile = $CurrentDB + "ToNewDB.bak"

$RelocateData = New-Object 
Microsoft.SqlServer.Management.Smo.RelocateFile($CurrentDB, $NewDBmdf)

$RelocateLog = New-Object 
Microsoft.SqlServer.Management.Smo.RelocateFile($CurrentDBlog, $NewDBldf)

Restore-SqlDatabase -ServerInstance $SQLServer -Database $NewDB -BackupFile 
$backupfile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData, 
$RelocateLog)

I cannot find an example of what I am trying to do. I have seen many examples of recovering databases with the same name but with different files. I want a different name and different file names. I am open to suggestions.

Answer 1

You do not need to use SMO just because you are in PowerShell.

import-module sqlps

$database = "NewDb"
$backupLocation = "D:\Backups\CurrentDB.bak"
$dataFileLocation = "D:\Databases\NewDB.mdf"
$logFileLocation = "D:\Logs\NewDB_log.ldf"

$sql = @"

USE [master]

ALTER DATABASE [$database] 
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [$database] 
FROM DISK = N'$backupLocation' 
WITH FILE = 1,  
     MOVE N'CurrentDB' TO N'$dataFileLocation',  
     MOVE N'CurrentDB_log' TO N'$logFileLocation',  
     NOUNLOAD, REPLACE, STATS = 5

ALTER DATABASE [$database] 
    SET MULTI_USER
"@

invoke-sqlcmd $sql

sqlps, System.Data.SqlClient Powershell TSQL.