Add a OutFileMapping switch and output to Get-DbaDatabaseFile

[Stuart-Moore]

Feature Request

Problem to solve

Add an easy way to build the FileMapping Hashtable for Restore-DbaDatabase from an existing database

For example:
$FileMapping = Get-DbaDatabaseFile -SqlInstance Instance1 -Database db1 -OutFileMapping

Would return $FileMapping of type Hashtable like this:

$FileMapping = @{
"Datafile1"  = "c:\data\datafile1.mdf";
"Datafile2" = "d:\ArchiveData\File2.mdf";
"LogFile1" = "e:\LogFiles\Log1.ldf"}

[ChrisTuckerNM]

Would liek to pick up this issue, any objections?

[potatoqualitee]

please do 👍

[ChrisTuckerNM]

that was odd. lol

[ChrisTuckerNM]

Questions:

1. Should the command be called with multiple SqlInstance values, what would the behavior be?
2. Does select-object @{Name="name"; Expression={$.LogicalName}},@{Name="value"; Expression={$.PhysicalName}} cover the requirement?

[Stuart-Moore]

Morning,
1- Good question. Personal preference would be for a custom object along the lines of:
[PSCustomObject]@{
ComputerName
InstanceName
Database
MappingHash
}

Whilst this usecase doesn't really support multiple dbs (Restore won't use hash for moves atm if more than 1 db is specified, though that's not a breaker, so it could be implement post this) this one would be consistent with dbatools practice, and make it easy for the user to extract the ones they need with a pipe through Where-Object

Or we could just refuse multiple instances with this switch.........

2 - I don't think so. The Restore command is expecting a hashtable for all the files in a db whereas that would just provide the table for 1 file so the user would still need to concatenate them themselves

This option is really going to be adding another output option. The current code spits out an object per file, whereas this would be an object per database, so probably branch at L175, group by databasename and then looping through that.

[niphlod]

I don't think is good to change result format via a single switch.
Instead of changing behaviour and result format at this point I'd go for splitting an internal function that fetches the info per database (and can be reused by restore-dbadatabase) and is used within get-dbadatabasefile to give the same result as it is now.

[Stuart-Moore]

restore doesn't need to pull it as it's not used unless pushed in by the user. And you're right, problem is that Get-DbaDatabaseFile outputs at the file level, and the hashtable wants to built up at the db level so it's not a good fit. Get-DbaDatabaseFileMap as a new command? Would be nice to have some 'telemetry' in so we could see how many people are using the options we provided, I'm expecting the Mapping option in restore isn't used heavily as the other options cover most use cases. Think this just got raised as someone asked for it in slack a long time ago, so wasn't a hugely though out idea more a placeholder

…

On Fri, 20 Jul 2018 at 11:10 Simone Bizzotto ***@***.***> wrote: I don't think is good to change result format via a single switch. Instead of changing behaviour and result format at this point I'd go for splitting an internal function that fetches the info per database (and can be reused by restore-dbadatabase) and is used within get-dbadatabasefile to give the same result as it is now. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#3080 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFWoZqYdhPA-hC4Gwkl3BHHEUhXi8OW4ks5uIaymgaJpZM4RgT-d> .

[niphlod]

I don't expect much user would need it as it's really easy to build that up starting from get-dbadatabasefile straight. Get-DbaDatabaseFileMap as internal sounds good.

[Stuart-Moore]

Yeah, I vaguely remember that was the gist of the slack conversation.
Get-This | Select-This | Format-That
but was seen as too tricky by the other party.

Maybe just add examples to the CBH for restore-dbadatabase under the Filemapping parameter help and Get-DbaDatabaseFile in the example sections?

[ChrisTuckerNM]

I'm advocating an example in CBH too.

[ChrisTuckerNM]

Taking myself off this till we understand what..

[andreasjordan]

Interesting feature.

We only need info returned from Get-DbaDatabase:

$db = Get-DbaDatabase -SqlInstance SRV1B -Database AdventureWorks
$fileMap = @{}
foreach ($file in $db.FileGroups.Files) {
    $fileMap[$file.Name] = $file.FileName
}
foreach ($file in $db.LogFiles) {
    $fileMap[$file.Name] = $file.FileName 
}
[PSCustomObject]@{ 
    ComputerName = $db.ComputerName
    InstanceName = $db.InstanceName
    SqlInstance  = $db.SqlInstance
    Database     = $db.Name
    FileMap      = $fileMap
}

Do we agree on Get-DbaDbFileMap as the name of the new command?

[potatoqualitee]

Good command name, but does Get-DbaDbFile not fit the bill?

[andreasjordan]

We need that hash that is returned in FileMap.

[potatoqualitee]

oh ah! sorry, I skipped to the bottom. k, sounds good!

[andreasjordan]

@potatoqualitee : As you are creating a lot of new commands at the moment: Can you take my code (#3080 (comment)) and create the command around it?

I would think we only need parameters -SqlInstance, -SqlCredential and -Database, maybe allow piped input from Get-DbaDatabase as well.

[potatoqualitee]

I can indeed 👍🏼