Move-DbaDbFile

Want to see the source code for this command? Check out Move-DbaDbFile on GitHub.
Want to see the Bill Of Health for this command? Check out Move-DbaDbFile.

Synopsis

Moves database files from one local drive or folder to another.

Description

Moves database files from one local drive or folder to another.
It will put database offline, update metadata and set it online again.

Syntax

Move-DbaDbFile -SqlInstance <DbaInstanceParameter>
    [-SqlCredential <PSCredential>]
    -Database <String>
    [-FileType <String>]
    [-FileDestination <String>]
    [-DeleteAfterMove]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Move-DbaDbFile -SqlInstance <DbaInstanceParameter>
    [-SqlCredential <PSCredential>]
    -Database <String>
    [-FileToMove <Hashtable>]
    [-DeleteAfterMove]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Move-DbaDbFile -SqlInstance <DbaInstanceParameter>
    [-SqlCredential <PSCredential>]
    -Database <String>
    [-FileStructureOnly]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Move-DbaDbFile -SqlInstance sql2017 -Database dbatools -FileType Data -FileDestination D:\DATA2

Copy all data files of dbatools database on sql2017 instance to the "D:\DATA2" path.
Before it puts database offline and after copy each file will update database metadata and it ends by set the database back online

Example: 2

PS C:\> $fileToMove=@{
>> 'dbatools'='D:\DATA3'
>> 'dbatools_log'='D:\LOG2'
>> }
PS C:\> Move-DbaDbFile -SqlInstance sql2019 -Database dbatools -FileToMove $fileToMove

Declares a hashtable that says for each logical file the new path.
Copy each dbatools database file referenced on the hashtable on the sql2019 instance from the current location to the new mentioned location (D:\DATA3 and D:\LOG2 paths).
Before it puts database offline and after copy each file will update database metadata and it ends by set the database back online

Example: 3

PS C:\> Move-DbaDbFile -SqlInstance sql2017 -Database dbatools -FileStructureOnly

Shows the current database file structure (without filenames). Example: 'dbatools'='D:\Data'

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

-Database

The database to be moved.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-FileType

Define the file type to move; accepted values: Data, Log or Both.
Default value: Both
Exclusive, cannot be used in conjunction with FileToMove.

-FileDestination

Destination directory of the database file(s).

-FileToMove

Pass a hashtable that contains a list of database files and their destination path.
Key and value should be the logical name and then the path (e.g. 'db1_log' = 'D:\mssql\logs')

-DeleteAfterMove

Remove the source database file(s) after the successful move operation.

-FileStructureOnly

Return a hashtable of the Database file structure.
Modifying the hashtable it can then be utilized with the FileToMove parameter

-Force

Database(s) is set offline as part of the move process, this will utilize WITH ROLLBACK IMMEDIATE and rollback any open transaction running against the database(s).

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.