commands

^

Move-DbaDbFile

Author Claudio Silva (@claudioessilva), claudioeesilva.eu
Availability Windows, Linux, macOS

 

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.

Alias
Required True
Pipeline true (ByValue)
Default Value
-Database

The database to be moved.

Alias
Required True
Pipeline false
Default Value

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.

Alias
Required False
Pipeline false
Default Value
-FileType

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

Alias
Required False
Pipeline false
Default Value
Accepted Values Data,Log,Both
-FileDestination

Destination directory of the database file(s).

Alias
Required False
Pipeline false
Default Value
-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')

Alias
Required False
Pipeline false
Default Value
-DeleteAfterMove

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

Alias
Required False
Pipeline false
Default Value False
-FileStructureOnly

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

Alias
Required False
Pipeline false
Default Value False
-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).

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

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

Alias wi
Required False
Pipeline false
Default Value
-Confirm

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

Alias cf
Required False
Pipeline false
Default Value