Over the years we had an opportunity to migrate dozens of MS SQL Server databases. In most instances we migrate databases to a new server. Database backup and restore is the best technique to achieve that. However, when you need to move databases to the new location on the same server, backup and restore creates too much overhead. The below solution will do the job with as little downtime as possible.
-- #1 take database offline breaking all active connections
alter database DATABASENAME
set offline
with rollback immediate
go
-- #2 detach database
use master
go
sp_detach_db 'DATABASENAME'
go
-- #3 figure out the current data and log file locations
use DATABASENAME
go
sp_helpfile
go
-- #4 copy data and log files to the new location
-- #5 reattach database (make sure you specify correct paths to data and log files)
use master
go
sp_attach_db 'DATABASENAME','D:\SqlData\DATABASENAME.mdf','D:\SqlLogs\DATABASENAME_log.ldf'
go
-- #6 repeat step #3 to verify that the database is using the new files
use DATABASENAME
go
sp_helpfile
go