[New-Command] Request: Move-DbaDatabaseFile

[areis422]

Is this:

• feature
• bug:

System Details

• Operating system name and version: Windows Server 2012 R2
• Output from $PSVersionTable:

Name                           Value
----                           -----
PSVersion                      5.1.14409.1005
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14409.1005
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

• SQL Server version for source/target: 2012, 2014, 2016

Can a cmdlet be created that will take standard arguments that must include:
SqlServer/Instance
DestinationDataFilePath
DestinationLogFilePath

Request based on the in-place upgrade process. If SQL installed using standard paths, upon upgrade (ex. 2014 to 2016) user databases will remain in Drive:\Program Files\Microsoft SQL Server\MSSQL12.INSTANCENAME\MSSQL\Data.

IF upgrading with standard paths, the desired action would be to detach and reattach data files after moving to version specific directory, in this example Drive:\Program Files\Microsoft SQL Server\MSSQL13.INSTANCENAME\MSSQL\Data but MSFT leaves the files in the original version directory.

Example call to this new cmdlet would be:

Move-DbaDatabaseFiles -Instance 'localhost\InstanceName' `
-Database 'myDbName' `
-DestinationDataFilePath 'MDFDrive:\Program Files\Path\To\My DB Root\Data' `
-Destination 'LDFDrive:\Program Files\Path\To\My DB Root\Data'

1. Retrieve current path using Get-DbaDatabaseFile and retrieve physical path of mdf and ldf files
2. Move or Copy mdf files from ($DBFileObj | ?{$_.TypeDescription -eq 'ROWS'}).PhysicalPath to $DestinationDataFilePath\$($DBFileObj.LogicalName).mdf
3. Move or Copy ldf files from ($DBFileObj | ?{$_.TypeDescription -eq 'LOG'}).PhysicalPath to $DestinationDataFilePath\$($DBFileObj.LogicalName).ldf
4. Offline/Single User mode the Database
5. Detach database/db files from SQL Instance
6. Attach database/db files to SQL instance using new path
7. Upon success dbcc, delete original files $DBFileObj | %{ Remove-Item -Force -Path $_.PhysicalPath}

[wsmelton]

You would have to exclude AG databases from this command as those require special methods that involve all replicas. Moving a file on a secondary replica can be done but it will suspend data movement until the AG is failed over to that replica.

If you are doing this AFTER the in-place upgrade is completed you don't have to detach/attach the database. This would work as well:

1. Retrieve paths of database files
2. Update paths to new directory
3. Take database offline (you can't use single user mode)
4. Copy files to new directory
5. Bring database online
6. Confirm path of database files
7. Drop files from the old directory.

[You do have to take the database offline, setting it to single user mode would still mean the database is being accessed so the files would be locked.]

[SQLDBAWithABeard]

I have had several requests for this command this week at SQL Bits and also requests for Move-DbaSystemDatabaseFile as well so I am bumping up this one

[niphlod]

you can pretty much extend rename-dbadatabase's code for the order of operations and quirks (at least for user databases). There are also bits and pieces lying around but they surely need a good reimplementation (like trashing robocopy, for starters)