Moving SQL user database files to the new location on the same server04/13/2011
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