Method #1:

-- This script uses a method described in the article:
-- "How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server"
-- http://support.microsoft.com/kb/224071
USE master
GO
IF DB_ID('MyDatabase') IS NOT NULL EXEC sp_detach_db MyDatabase
GO
sp_attach_db 'MyDatabase','C:\Data\MyDatabase.mdf','C:\Data\MyDatabase.ldf'
GO

Method #2:

USE master
GO
IF DB_ID(N'MyDatabase') IS NOT NULL EXEC sp_detach_db MyDatabase
GO
CREATE DATABASE MyDatabase
ON (FILENAME='C:\Data\MyDatabase.mdf'),
   (FILENAME='C:\Data\MyDatabase.ldf')
FOR ATTACH
GO