Powershell restore SQL Server database to new database

Question

I have a database $CurrentDB and I want to restore a backup of $CurrentDB to $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 attempting to user Restore-SqlDatabase but I don't know how to properly -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 can't seem to locate an example of what I am attempting to do. I have seen plenty of examples of restoring databases with the same name but different files. I want a different name and different file names. I am open to suggestions.

David Browne - Microsoft · Accepted Answer · 2017-08-18 21:15:47Z

9

You don't have to use SMO just because your're 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

And if you don't have sqlps installed, you can use System.Data.SqlClient from Powershell to run TSQL.

answered Aug 18, 2017 at 21:15

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Der_Meister · Accepted Answer · 2019-03-04 04:28:43Z

3

$RelocateData = [Microsoft.SqlServer.Management.Smo.RelocateFile]::new($CurrentDB, $NewDBmdf)
$RelocateLog = [Microsoft.SqlServer.Management.Smo.RelocateFile]::new($CurrentDBlog, $NewDBldf)

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

answered Mar 4, 2019 at 4:28

Der_Meister

5,1072 gold badges49 silver badges57 bronze badges

Collectives™ on Stack Overflow

Powershell restore SQL Server database to new database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related