Is it possible to move sql server databases (physical files) without detach/attach

Question

I want to move physical location of all my databases (around 20) to new location.

sys.master_files (physical name) column gives me path of databases.

1. If I copy physical files to new location and change path here then databases will be moved to new locations?

2. If above is not possible then what is the best way to move databases in bulk?

Thanks.

Answer 1

Yes it is possible, please check this way (Source: TechNet)

Thank a Full backup first!

Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.

ALTER DATABASE database_name SET OFFLINE;

Move the files to their new locations. Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

ALTER DATABASE database_name SET ONLINE;