Un database SQL è composto principalmente da due file fisici: un file di dati ".MDF" ed un file per il log delle transazioni ".LDF".
Supponiamo di voler spostare in una posizione differente i due files; come procedere?
Abbiamo due strade, o attraverso la stored procedure di sistema sp_attach_db o attraverso il comando di Restore Database.

Prima di tutto creiamo un database di prova utilizzando il metodo Create Database senza nessun parametro se non il nome del database stesso.

USQ master
GO 
CREATE DATABASE myTest
GO 

SQL creerà il database myTest insieme al file primario e al file di log delle transazioni corrispondente. Non avendo specificato nessun parametro le dimensioni del file primario del database corrispondono a quelle del file primario del database model e le dimensioni del file di log delle transazioni corrispondono alle dimensioni del file primario. La posizione dei due files su disco sarà quella di default specificata nell'istallazione di SQL.
Per conoscere, comunque, la posizione dei due files posso usare sp_help_db consultando la colonna filename.

USE Master
GO
EXEC sp_helpdb mytest
GO

Supponiamo ora, di voler spostare i due files in due directory differenti e più precisamente in in D:\MSSQL\Data il file dati ed in E:\MSSQL\Log il file del log delle transazioni.
Analizziamo entrambe le soluzioni
 
sp_attach_db
Attraverso questo metodo attacchiamo fisicamente un file dati ed il file dei log delle transazioni, al servizio di SQL Server.
Prerogativa essenziale è che, prima di procedere, il database sia stato sganciato dal servizio SQL attraverso la sp_detach_db e che i files siano stati spostati nella nuova posizione.

--Sgangio il database
USE Master
GO
EXEC sp_detach_db 'mytest', 'true'
GO
--Aggancio il database nella nuova posizione
EXEC sp_attach_db @dbname = N'myTest', 
   @filename1 = N'D:\MSSQL\Data\myTest.mdf',
   @filename2 = N'E:\MSSQL\LOG\myTest_log.ldf'
GO

Restore Database
Personalmente preferisco questa opzione, anche perchè posso eseguirla senza dover copiare fisicamente i files e senza sganciare il database dal servizio SQL.
Prima di tutto bisogna effettuare  un backup del database.

--Eseguo il backup
BACKUP DATABASE myTest 
TO  DISK = N'C:\temp\myTest.bak' 
WITH NOFORMAT, INIT
GO
/*
Se non conosco le informazioni sulla posizione ed i nomi logici dei 
files utilizzo l'opzione FileListOnly
*/
RESTORE FILELISTONLY
FROM DISK ='c:\temp\myTest.bak'
GO
--Recuperato il LogicalName dei file mdf e ldf faccio il restore
--nella nuova posizione
RESTORE DATABASE myTest
FROM DISK ='C:\Temp\myTest.bak'
WITH
    MOVE 'myTest' TO 'D:\MSSQL\Data\myTest.mdf',
    MOVE 'myTest_Log' TO 'E:\MSSQL\LOG\myTest_Log.ldf'
GO