Move-sqmDatabaseFile

Maintenance sqmSQLTool v1.9.147+ · Move ✓ -WhatIf supported

Moves the files of a database (MDF, NDF, LDF) to another drive or directory and runs exactly the steps SQL Server requires for it. Pass just the database name to move all its files, -FileType Data or -FileType Log for one kind, or -LogicalFileName to move a single file.

Why this is not a wrapper around Move-DbaDbFile: that cmdlet refuses master, model, msdb and tempdb outright ("System database detected as input"), and tempdb is precisely the database you want to move when a drive runs out of space. It also has no write probe, no space check and no -WhatIf.

tempdb leaves its old files behind. SQL Server does not clean them up on restart; it creates new files at the new location and the old ones simply stay on the old drive. This function removes them, but only after the instance is back and the new path is confirmed.

The three flows

Database	Sequence	Files copied
`User database`	SET OFFLINE → copy → `ALTER DATABASE ... MODIFY FILE` → SET ONLINE	yes
`tempdb`	`MODIFY FILE` → instance restart → remove old files	no, tempdb is recreated at startup
`model` / `msdb`	`MODIFY FILE` → stop the engine → copy → start the engine	yes
`master`	Refused. Its file paths live in the startup parameters `-d`/`-l`/`-e`, not in `ALTER DATABASE`. Use `Set-DbaStartupParameter`.	—

Parameters

Parameter	Type	Required	Default	Notes
-SqlInstance	string	Optional	$env:COMPUTERNAME	Target SQL Server instance.
-SqlCredential	PSCredential	Optional	,	SQL authentication instead of the current Windows context.
-Database	string	Required	,	Name of the database whose files are moved, for example `SalesDB` or `tempdb`.
-FileDestination	string	Required	,	Target directory, interpreted from the SQL Server's point of view, not from the machine running the command.
-LogFileDestination	string	Optional	-FileDestination	Separate target directory for log files, so data and log can go to different drives in one call.
-FileType	string	Optional	All	`All`, `Data` (MDF/NDF) or `Log` (LDF). Ignored when `-LogicalFileName` is given.
-LogicalFileName	string[]	Optional	,	Logical names of individual files, for example `tempdev` or `SalesDB_log`. Moves just those instead of the whole database.
-Credential	PSCredential	Optional	,	Windows credential for the file operations and the service control on the target host. Only needed for a remote instance the current context cannot reach.
-KeepOldFiles	switch	Switch	$false	Leave the files at the old location instead of removing them after a successful move.
-NoRestart	switch	Switch	$false	tempdb only. Writes the new paths but does not restart, so the change takes effect at the next restart (Status `PendingRestart`) and the old files stay. Refused for `model`/`msdb`, where the instance would not come up again.
-SkipWriteProbe	switch	Switch	$false	Skip the write probe in the target directory. Only sensible if creating a temporary database is unwanted for audit reasons.
-SkipSpaceCheck	switch	Switch	$false	Skip the free-space check at the target.
-SpaceBufferPercent	int	Optional	20	Safety margin added to the required size in the space check.
-RestartTimeoutSeconds	int	Optional	300	How long to wait for the instance to answer again after a service action.
-Force	switch	Switch	$false	Kills open connections when taking a user database offline, and overrides the Always On block.
-EnableException	switch	Switch	$false	Throw instead of returning a result row with Status `Failed`.
-WhatIf / -Confirm	switch	Optional	,	ShouldProcess with `ConfirmImpact = 'High'`. All prechecks still run under `-WhatIf`, so a dry run tells you whether the move would work.

What is checked before anything changes

Check	Why it matters
Database state	Must be `ONLINE` or `OFFLINE`. Snapshots are refused, `master` is refused.
Always On membership	An AG database cannot be taken offline; the files have to be moved replica by replica. Resolved through `Get-sqmDatabaseAgMembership`, which distinguishes "not a member" from "could not be determined" instead of silently treating both as safe. `-Force` overrides.
Target directory visible	`Test-DbaPath` asks the instance (`xp_fileexist`), not the calling machine. A missing directory is created, the ACL of the source directory is copied onto it, and the engine service account is granted full control explicitly, because a fresh folder only inherits the ACL of the drive root.
Write probe	An 8 MB database is created in the target directory and dropped again immediately. Only that proves the service account may create files there; a readable ACL or a visible path does not. For tempdb this is the decisive test: a tempdb path the service cannot write means the instance will not start after the restart. The failure looks like `CREATE FILE encountered operating system error 5(Access is denied.)`.
Free space	Sum of the file sizes plus `-SpaceBufferPercent` against the free space at the target, via `Get-DbaDiskSpace` (which knows mount points), falling back to `xp_fixeddrives`.

If a check fails, nothing is changed at all. If the copy fails halfway through, every MODIFY FILE already issued is reverted and the database goes back online on its old paths. After a service action the SQL Server Agent is put back into the state it was in before, because Start-DbaService -Type Engine does not bring it along and a silently stopped agent is only noticed when the nightly backup fails to run.

Execution Flow

Result status

One object per step, each carrying SqlInstance, Database, Step (Precheck, Move, Restart, Cleanup), LogicalName, OldPath, NewPath, SizeMB, Status and Message.

Status	Meaning
`Success`	The step completed.
`AlreadyInPlace`	The file already sits in the target directory. Nothing was done for it.
`Blocked`	Refused before any change: `master`, a snapshot, an AG database without `-Force`, `-NoRestart` for model/msdb, or an unanswerable Always On question.
`Aborted`	A precheck failed. Nothing was changed.
`Failed`	The step failed. The message names the reason.
`RolledBack`	A `MODIFY FILE` that had already been issued was set back to the old path after a later file failed.
`PendingRestart`	tempdb with `-NoRestart`: the paths are written, the change takes effect at the next restart, the old files were left in place.
`NotFound`	The database, or a name given via `-LogicalFileName`, does not exist.
`Warning`	Worth knowing but not blocking, for example a failover cluster instance or free space that could not be determined.
`Skipped`	A check was switched off, or the old files were kept via `-KeepOldFiles`.
`WhatIfSkipped`	`-WhatIf`: this is what would have happened.

Examples

Move tempdb to its own drive, restart, clean up the leftovers

Move-sqmDatabaseFile -SqlInstance "SQL01" -Database "tempdb" -FileDestination "G:\MSSQL\TempDB"

Dry run first: all prechecks run, nothing is changed

Move-sqmDatabaseFile -SqlInstance "SQL01" -Database "tempdb" -FileDestination "G:\MSSQL\TempDB" -WhatIf |
    Format-Table Step, LogicalName, NewPath, Status, Message -Wrap

Move only the log file of one user database

Move-sqmDatabaseFile -SqlInstance "SQL01" -Database "SalesDB" `
    -LogicalFileName "SalesDB_log" -FileDestination "H:\MSSQL\Log"

Split data and log onto separate drives, killing open sessions

Move-sqmDatabaseFile -SqlInstance "SQL01" -Database "SalesDB" `
    -FileDestination "G:\MSSQL\Data" -LogFileDestination "H:\MSSQL\Log" -Force

Write the paths now, restart in the next maintenance window

Move-sqmDatabaseFile -SqlInstance "SQL01" -Database "tempdb" `
    -FileDestination "G:\MSSQL\TempDB" -NoRestart -Confirm:$false

Remote instance, separate Windows credential for files and service

$win = Get-Credential
Move-sqmDatabaseFile -SqlInstance "SQL01\INST01" -Database "tempdb" `
    -FileDestination "G:\MSSQL\TempDB" -Credential $win

⬆ Index

All Commands

📊 Check First

Get-sqmDiskSpaceReport