How move database to other disk

Product:
Microsoft SQL server

Issue:
How to simple move database file to other hard disk?

Suggested solution:

There are many ways to do this task, this is one of them:

Detach the database with a SQL query:

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventure
SET SINGLE_USER
GO 

-- Detach the database
sp_detach_db 'Adventure'
GO

 

Copy the files with robocopy from a command prompt:

Syntax are : ROBOCOPY source destination [file [file]…] [options]

The /MOV parameter will move the file, if you leave it out – there will be a copy instead.

This will move file adventure.mdf from C to D drive.

robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"  D:\Data   adventure.mdf

This will move file adventure.ldf from C to L drive.

robocopy /MOV "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA"  L:\Log   adventure.ldf

You need to put file path in ” if they contain spaces.

Attach the database with a SQL query:

USE master
GO

-- Attach the database
sp_attach_DB 'Adventure', 
'D:\data\Adventure.mdf',
'L:\log\Adventure.ldf'
GO

 

More Information:

Robocopy does NOT like trailing \

The Ultimate Guide to Robocopy

https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/1774/move-sql-server-transaction-log-files-to-a-different-location-via-tsql-and-ssms/

How to Move Log File or MDF File in SQL Server? – Interview Question of the Week #208