亂馬客

--可以的話，請先將資料庫備份
USE master;
GO
-- Take database in single user mode 
-- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO
–- COPY FILES TO NEW LOCATION
EXEC xp_cmdshell 'COPY “C:\OriginalLocation\TestDB_log.ldf” “C:\NewLocation\TestDB_log.ldf”'
GO
EXEC xp_cmdshell 'COPY “C:\OriginalLocation\TestDB.mdf” “C:\NewLocation\TestDB.mdf”'
GO

-- Re-Attached DB
CREATE DATABASE [TestDB] ON
( FILENAME = N'C:\NewLocation\TestDB.mdf' ),
( FILENAME = N'C:\NewLocation\TestDB_log.ldf' )
FOR ATTACH
GO

–- DELETE THE OLD DATABASE FILES
EXEC xp_cmdshell 'DEL /Q “C:\OriginalLocation\TestDB_log.ldf”'
GO
EXEC xp_cmdshell 'DEL /Q “C:\OriginalLocation\TestDB.mdf”'
GO 

USE master;
GO
–-SET DATABASE OFFLINE
ALTER DATABASE [TestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

–- COPY FILES TO NEW LOCATION
EXEC xp_cmdshell 'COPY “C:\OriginalLocation\TestDB_log.ldf” “C:\NewLocation\TestDB_log.ldf”'
GO
EXEC xp_cmdshell 'COPY “C:\OriginalLocation\TestDB.mdf” “C:\NewLocation\TestDB.mdf”'
GO

–- ALTER DATABASE MODIFY FILE NAME
–- LOG
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB_log’, FILENAME = ‘C:\NewLocation\TestDB_log.ldf’ )
GO
–- DATA
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB’, FILENAME = ‘C:\NewLocation\TestDB.mdf’ )
GO

–- SET DATABASE ONLINE
ALTER DATABASE [TestDB] SET ONLINE
GO

–- OPTIONAL
–- CHECK INTEGRITY
DBCC CHECKDB(‘TestDB’)
GO

–- DELETE THE OLD DATABASE FILES
EXEC xp_cmdshell 'DEL /Q “C:\OriginalLocation\TestDB_log.ldf”'
GO
EXEC xp_cmdshell 'DEL /Q “C:\OriginalLocation\TestDB.mdf”'
GO