0

I'm trying to do a restore of a database on the same instance under a different Name and I need to replace the physical filenames of data and log files. To do this I want to use the -FileMapping parameter but I keep getting

File xxx.MDF already exists on yyyy and owned by another database, cannot restore

Here is a portion of the script

$FileStructure = @{
'Database_data' = "$DestinationDataDir\dm\$X_DBName.mdf"
'Database_Log' = "$DestinationLogDir\dm\$X_DBName.ldf"
}
Write-Output $FileStructure

#Restore-DbaDatabase -SQLServer $Instance -Path $BackupPath - 
DestinationDataDirectory $DestinationDataDir -DestinationLogDirectory    
$DestinationLogDir -DatabaseName $X_DBName  -ReplaceDbNameInFile - 
WithReplace  -OutputScriptOnly
Restore-DbaDatabase -SQLServer $Instance -Path $BackupPath -FileMapping  
$FileStructure -DatabaseName $X_DBName   -WithReplace  -OutputScriptOnly

Write-Output $FileStructure, $BackupPath

Name Value
---- -----
Database_data E:\Data2016\dmu\dm\X_ENTWICKLUNG_DM.mdf
Database_Log F:\Log2016\dmu\dm\X_ENTWICKLUNG_DM.ldf

I had a look at the docs (see sample 15)` but I must be doing something else wrong.

Edited: In SSMS it Looks like thisSSMS_Picture So the files under "Restore As" need a different name. That's what I want to achive.

Does anybody know how to fix the PowerShell to do this? I must be missing something stupid I guess. Thanks

dim
  • 136
  • 1
  • 12
  • Just to be clear. You have a backup you want to restore and want the physical files to be named like the database you are restoring into - right? – Mötz Dec 06 '18 at 21:19
  • Yes, the backup is from another database from the same instance and should be restored on the same instance under another database name – dim Dec 07 '18 at 09:14
  • Then me answer should be able to help you. Please comment if things are unclear or does not work. – Mötz Dec 07 '18 at 09:21
  • 1
    I think you nailed it but I still get the error. It's exactly the same error you would get in SSMS if you try to restore the same DB under a different DB_Name but leave the physical files unchanged. So for some reason PowerShell does not replace the file names properly but use the original ones which will not work using the same directory – dim Dec 07 '18 at 09:44
  • Could it be that the physical files from the "source" database isn't name correctly on the file system. Please make sure what the source database and it's physical files are named, so we don't bark up the wrong tree. – Mötz Dec 07 '18 at 22:02
  • I would recommend you to accept the answer if you feel it helped you with the task. Then you can come back and update it with you details later when you have time for that. – Mötz Dec 11 '18 at 16:59

2 Answers2

2

I would like to resume the before response. For me, It works!:

Restore-DbaDatabase -SqlInstance localhost -DatabaseName NewDatabase -Path 'C:\MyPath\MyDatabase.bak' -WithReplace -ReplaceDbNameInFile

That´s it.

Thanks @Mötz

Elkin Cruz
  • 71
  • 1
  • 3
1

Assuming that you have a backup file from the "X_ENTWICKLUNG_DM" database from the same server, and you want restore it back into another database. If the value from the $X_DBName is the same as the original database, that will be the reason for the exception you are facing.

This will restore the database into the default paths for DATA and LOG, without you having to know about them upfront:

Restore-DbaDatabase -SqlInstance $Instance -Path $BackupPath -DatabaseName $NEWDBNAME -ReplaceDbNameInFile -WithReplace

The $NEWDBNAME variable has to be something else than the original database you took the backup from and are now trying to restore back in.

If you want the logical file names to be name like the name of the new database you restored into, you should do something like this: 

Rename-DbaDatabase -SqlInstance . -Database $NEWDBNAME -LogicalName "<DBN>_<FT>"

Update

I just created an new database on my lab server. I named it TEST. The database is empty, but it serves the purpose of testing my code.

I took a full backup of the database with this command:

Backup-DbaDatabase -SqlInstance localhost -Database Test -BackupDirectory C:\Temp\ -CopyOnly -Type full -CompressBackup

After the creation of the backup file, I tested the content of the backup and explicit the filelist, to see what default names the backup would suggest when wanting to restore the backup file:

Get-DbaBackupInformation -SqlInstance localhost -Path C:\Temp\Test_201812072244.bak | Format-list filelist

FileList : {
@{Type=D; LogicalName=Test; PhysicalName=D:\MSSQL\DATA\Test.mdf}, 
@{Type=L; LogicalName=Test_log; PhysicalName=D:\MSSQL\LOG\Test_log.ldf}
}

After the confirmation about the default file list from the backup file, I ran the below code to generate the raw T-SQL script that the module is going to execute:

Restore-DbaDatabase -SqlInstance localhost -DatabaseName Test_Restored -Path C:\Temp\Test_201812072244.bak -WithReplace -ReplaceDbNameInFile -OutputScriptOnly

RESTORE DATABASE [Test_Restored] FROM  DISK = N'C:\Temp\Test_201812072244.bak' WITH  FILE = 1,  MOVE N'Test' TO N'D:\MSSQL\DATA\Test_Restored.mdf',  MOVE N'Test_log' TO 'D:\MSSQL\LOG\Test_Restored_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

The -OutputScriptOnly parameter to the Restore-DbaDatabase cmdlet will not execute the script, only generate it and output it to your console.

Please note that the file names it is going to restore is actually renamed to be the names of the new database that I'm trying to restore into.

I would recommend that you create an empty database and play around with that, until your commands executes and behaves as expected. I would recommend that you run an update, just in case.

Update-Module dbatools -Force

While you troubleshoot, please try and execute the Get-DbaBackupInformation cmdlet to help you investigate the issue you're facing.

Mötz
  • 1,682
  • 11
  • 17
  • Thanks. I've tried that as well and it still throws the same error. Edited my question to help understand the problem – dim Dec 07 '18 at 09:22
  • Sorry for the late reply. Your suggestions help me solve the problem which was in fact a problem of differences between database name, logical name and physical names. Unfortunately there is no warning about differences here running theses commands. I will update your answert to incorporate my findings and then mark it as an anwer. – dim Dec 10 '18 at 09:39
  • Glad you found the issue and was able to get going. Looking forward to see what the issue was :) – Mötz Dec 10 '18 at 13:13
  • This doesn't seem to be working if an underscore is present in the database name, is that correct? – MattV Feb 04 '20 at 23:16
  • @MattV I believe you either need to share more context or start a new question and simply referer this question to provide some background context for your new question. – Mötz Feb 05 '20 at 07:55