Add "WITH MOVE" configuration to database restore

[franhoey]

Is your feature request related to a problem? Please describe.
When restoring MS SQL Server databases the log and data files are restored to the same file location they held on the computer where the backup was made. If the database is restored to a different computer and the folder structure defined by the original file locations do not exist, the restore fails.
This becomes an issue when working in a team who do not have a standard setup or restoring data between local machines and alternatively configured test or production environment servers. Or in my case when I tried to execute the restore on a newly built laptop.

Describe the solution you'd like
This is tricky, because grate needs to be agnostic to database type.
My suggestion is to allow a mix of data formats to the Restore configuration item. Either a simple string as it currently is to the backup file, or a structured string similar to the Folder configuration (name-value separated by semi-colon) to add additional parameters e.g. "file=C:/path/file.bak;move=database|D:/data/filename.mdf;move=database_log|D:/logs/filename.ldf"

Describe alternatives you've considered
We could use JSON in the configuration value, but this hasn't been used in other configurations
New configurations could be added e.g. RestoreMove, but this would then be specific to MS SQL Server, where as a configuration string format in the Restore configuration setting would allow specific config to be used on other database types in the future

Additional context
N/A

[franhoey]

I get time in my job when things are slow I could use to complete this issue it that is helpful

[erikbra]

Hi, @franhoey . This sounds like a good feature.

Restore is actually only available for SQL Server, so if we can make this feature sensibly for SQL server, that would be good enough. To understand, what you are suggesting, is instead of only allowing one backupPath in the config, we should allow both:

1. a list of backup configs
2. for each config, either:
   a. a single folder (which is used to restore too)
   b. a config with both "source" and "target" folder

Did I understand it correctly?

Do you have any suggestion to how we could enhance the restore from backup SQL to cater for all of the above?

https://github.com/erikbra/grate/blob/9549a12d1a3d779b8057f60f47d2fa49a3d5308b/src/grate.sqlserver/Migration/SqlServerDatabase.cs#L91