PowerShell script exchange
Post Reply
inoisikoiski
Influencer
Posts: 17
Liked: never
Joined: Apr 12, 2019 9:55 am
Full Name: Inoi Visp
Contact:

SQL restore database with multiple datafiles on different drive using PS

Post by inoisikoiski » Apr 23, 2019 2:01 pm

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
Veeam Software
Posts: 3777
Liked: 456 times
Joined: Sep 01, 2014 11:46 am
Location: Austria
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by HannesK » Apr 23, 2019 2:46 pm

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
Veeam Software
Posts: 97
Liked: 23 times
Joined: Apr 20, 2017 4:19 pm
Full Name: Joe Houghes
Location: Austin, TX
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by jhoughes » Apr 23, 2019 2:54 pm

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.
Husband, Father, Solutions Architect, Geek Extraordinaire | @AustinVMUG & @ATXPowerShell leader | vExpert | Cisco Champion | Tech Field Day Delegate | VCP 4/5/6 | VCP6-NV | VMware/Cisco UCS/Powershell/VEEAM Enthusiast

inoisikoiski
Influencer
Posts: 17
Liked: never
Joined: Apr 12, 2019 9:55 am
Full Name: Inoi Visp
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by inoisikoiski » Apr 23, 2019 2:59 pm

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
Veeam Software
Posts: 97
Liked: 23 times
Joined: Apr 20, 2017 4:19 pm
Full Name: Joe Houghes
Location: Austin, TX
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by jhoughes » Apr 23, 2019 3:02 pm

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.
Husband, Father, Solutions Architect, Geek Extraordinaire | @AustinVMUG & @ATXPowerShell leader | vExpert | Cisco Champion | Tech Field Day Delegate | VCP 4/5/6 | VCP6-NV | VMware/Cisco UCS/Powershell/VEEAM Enthusiast

inoisikoiski
Influencer
Posts: 17
Liked: never
Joined: Apr 12, 2019 9:55 am
Full Name: Inoi Visp
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by inoisikoiski » Apr 23, 2019 3:33 pm

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
Product Manager
Posts: 16778
Liked: 1405 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by veremin » Apr 23, 2019 5:29 pm 1 person likes this post

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
Influencer
Posts: 17
Liked: never
Joined: Apr 12, 2019 9:55 am
Full Name: Inoi Visp
Contact:

Re: SQL restore database with multiple datafiles on different drive using PS

Post by inoisikoiski » Apr 24, 2019 10:57 am

Thanks! That made sense. Got my script working.

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest