The following scripts will detach and then reattach the Test database
- Find the path of the database
sp_helpdb Test
- Set the database to a single user mode
USE [master]
GO
ALTER DATABASE [Test]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
- Detach the database using the sprocs
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’Sales’, @skipchecks = ‘false’
GO
- Now move the files from loc1 to loc2 after that you are able to reattach the files with new locations with below command
USE [master]
GO
CREATE DATABASE [Test] ON
( FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASales.mdf’ ),
–new location .mdf file path
( FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASales_log.ldf’ )
–new location .ldf file path
FOR ATTACH
GO