SQL restore database with multiple datafiles on different drive using PS

[inoisikoiski]

Hi,
As stated in the subject, I am trying to restore a sql database with multiple datafiles (.mdf, .ndf) to a different location and the logfiles to another location. I just want the drive (location) as an input. Can anyone help please.
Thanks

[HannesK]

Hello,
I moved your post to the powershell forum.

Could you describe a little bit more which commands you are using? Are you using Restore-VESQLDatabase ?

Which parameters are you using? As far as I can see there is no need to specify the log files path but you can use TargetPath if you require multiple locations for the database files.

Best regards,
Hannes

[jhoughes]

Hannes is describing example #3 in the documentation here to be able to specify file paths to restore your database files, and it will allow different paths per file.

https://helpcenter.veeam.com/docs/backu ... l?ver=95u4

You could add some logic to build out a full path based on an input of drive letter, which would then be used as your target path for the restore.

[inoisikoiski]

Thanks Hannes. I am indeed using Restore-VESQLDatabase.

The thing is, for example, the production datafile location is on E:\SQLData and logfile is L:\SQLlog. And in our DEV servers, it's D:\SQLData and L:\SQLlogs. I tried using the -TargetFolder but all datafiles were in all location. Also tried the -TargetPath however, i managed to make it work when there is only one datafile (.mdf) and one logfile. My problem with -TargetPath is if the database has multiple datafiles.

Thanks again.

[jhoughes]

You need to create an array of file paths for every file that you want to restore, and the file order should match the array that is returned when you run the 'Get-VESQLDatabaseFile' cmdlet.

[inoisikoiski]

Sorry, not really a hardcore on PS. I'm trying to figure out how when using -TargetPath will it know which is which (mdf, ndf, ldf etc)

[veremin]

You will have two arrays:

-File <VESQLDatabaseFile[]>
-TargetPath <string[]>

Say, a first element in first array is .mdf file. To place .mdf file in "C:\Temp\" directory you will need to make sure that "C:\Temp" string is the first element of the second array.

Thanks!

[inoisikoiski]

Thanks! That made sense. Got my script working.