I am trying to restore a database to a VM as part of a new VM provisioning process. However, when I try the following command to restore the database:
Restore-SqlDatabase -ServerInstance . -Database SomeDatabase -BackupFile $latestBackup -ReplaceDatabase
It fails with the error:
Restore-SqlDatabase : System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SomeDatabase.mdf" failed with the operating system error 3(The system cannot find the path specified.).
At line:27 char:1
+ Restore-SqlDatabase -ServerInstance . -Database SomeDatabase -BackupFile $latestB ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
This is confusing, since using SQL Server 2014 has the exact same path, but at MSSQL12.MSSQLSERVER instead of the MSSQL10.MSSQLSERVER that the Restore-SqlDatabase cmdlet is looking for. A database with the correct name already exists at the MSSQL12.MSSQLSERVER location as well.
How can I tell the cmdlet to restore it to the right folder?
Turns out it isn't really possible (that I could find) to restore a database like this. I reverted the VM to use a matching SQL server, and used the following command to restore the database:
Invoke-Sqlcmd -Query "RESTORE DATABASE [SomeDatabase] FROM DISK = N'C:\$latestBackup' WITH FILE = 1, MOVE N'SomeDatabase' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SomeDatabase.mdf', MOVE N'SomeDatabase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SomeDatabase.ldf', NOUNLOAD, STATS = 10"
This was obtained via SQL Server Management Studio's "Script" functionality, where I set up the restore and exported the equivalent SQL.