Question:
I have a database $CurrentDB and I want to restore a backup of $CurrentDB to $NewDB. The T-SQL command looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$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.
Answer:
You don’t have to use SMO just because your’re in PowerShell.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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.