I’ve used sp_detach_db and sp_attach_db to relocate database files for many years. I know that sp_attach_db was deprecated in SQL 2005 but, like most DBAs, I’ve continued to use sp_attach_db mostly out of habit. I want to share with you why I’ve decided to change my ways.
Planned File Relocation
Let’s say you want to move the log file from to a separate drive. The following script shows how to accomplish in SQL Server 2000 using sp_attach_db. The only sp_attach_db parameters required are the database name, primary data file path and the log file that was moved from the original location.