Move SQL Server database files to another drive & folder

This post demos a script I put together to help move SQL Server database files to another drive and folder by generating PowerShell and T-SQL commands.

I’ve decided to make this script for situations where installing the dbatools PowerShell module wouldn’t be possible.
Otherwise, I highly recommend using dbatools’ Move-DbaDbFile command.

Where’s the script?

It’s a bit too large for a blog post, but you can find the MoveDatabaseFiles.sql script in my SQL Server Scripts GitHub repo.

What it does

It generates T-SQL and PowerShell commands to move SQL Server database file(s) to the specified folder(s).

The script can handle both data files (mdf and ndf) and transaction log files (ldf).

Can be used to move both user database files as well as SQL Server’s tempdb database to another drive.

Safety features

• Creates destination folder(s) if missing.
• Sets proper permissions on the destination folder(s) for the service account. To avoid file access issues like the ones described in this post.
• The output can also be used to document your work for audits or change requests.

How to use it to move SQL Server database files to another drive and/or folder

1. Open the script in SQL Server Management Studio.
2. Provide input values for the relevant parameters as described in the following block of T-SQL:

• @DatabaseName – the name of the database whose files you want to move (mandatory)
• Providing a value for at least one of the following 2 parameters is mandatory:
  • @DataDestination (new path for .mdf/.ndf)
  • @TLogDestination (new path for .ldf)

3. Execute the script.
   This step won’t make any actual changes, it only generates the commands required to move the database files.
4. Execute the resulting commands as instructed by the output.
   This step will require some downtime, at least for one database (if you’re moving a database), if not for the whole instance (if moving tempdb).
   So, plan appropriately to make sure you’re not causing an outage.

Note:

• Providing a folder path to only one of the two destination parameters will make the script generate the required commands only for that type of database file.
• Providing the same folder path to both destination parameters will make the script generate commands to move both types of files to the same directory.
• The PowerShell commands need to be executed in PowerShell opened as Administrator on the same host where the SQL Server instance resides

Sequence of generated commands

To move user database files:

1. Set database offline (T-SQL)
2. Create the destination directories if they don’t exist (PowerShell)
3. Set folder permissions (PowerShell)
4. Move files (PowerShell)
5. Update SQL metadata (T-SQL)
6. Set database online (T-SQL)

To move tempdb files:

1. Create the destination directories if they don’t exist (PowerShell)
2. Set folder permissions (PowerShell)
3. Update SQL metadata (T-SQL)
4. Restart SQL Server service (PowerShell)
5. Clean old tempdb files (PowerShell)

Here’s an example of the output when opting to move the data and transaction log files of the StackOverflow database to a new drive and folders.

Conclusion

If you can’t use dbatools and need an easy way to handle all the steps required to move a SQL Server database’s files to another folder, then feel free to use this script.