Monday, May 12, 2014

sql server Replication and Move Database Files mdf and ldf to Another Location


Step 1 :  Stop log agent in primary DB replication located

Step 2 : Open Read or secondary DB , new query window --->USE MASTER;

Step 3 :  Run 

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE db_read
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Step 4 : -- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'db_read'
GO

Step 5 : Manually move the mdf and ldf Files from Loc1 to Loc 2

Step 6 : -- Re-Attached DB
CREATE DATABASE [db_read] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read_log.ldf' )
FOR ATTACH
GO

Step 7 : start the log agent in primary DB

No comments:

Post a Comment