Move replicated database file to new location in SQL Server 2012

Move replicated database file to new location in SQL Server 2012

Setting up replication is somehow little easy when we compare it with maintaining replication. We have already discussed some of the topics related to replication like set up replication, keep watch on replication latency, add & remove article from GUI and from Script, remove replication to name a few.

Moving replicated database file from one location to another location is little tricky and not performing proper steps in sequence would be resulted in replication failure.

There are many cases when we need to move file of database from one location to another. We may have purchased new improved IO subsystem or we are running low disk space is current drive and need to move all or some data/log file to another drive.

I will be using the same AdventureWorks2012 database which I have used in transactional replication I had in my earlier article.

Well, before we do anything, we have to confirm that there is no pending (Undistributed) transaction there in replication from “Replication Monitor”. If you don’t know how to open “Replication Monitor”, kindly click here to know.

After having replication monitor on the screen, click on “Publisher Name” then click on “Subscriber Name” from the tree view in left hand side. Once “Subscriber” is selected, you find, “All Subscriber” tab in right hand side, double click on the subscriber where your database is participated in. You have to confirm undistributed command is zero.

Once you confirm undistributed commands, close the popup window of Subscription and click on “Publisher” name in the tree view of “Replication Monitor”. Let this window open and start SSMS to confirm where data/log file are located at the moment.

I have used following TSQL query to check current location of my database “AdventureWorks2012”.

 SELECT * FROM sys.sysaltfiles
 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
 GO
 

I have my database files in “C” drive.

Once, you keep this list of data/log file location, let us again move back to “Replication Monitor” and stop “Log Reader Agent”.

Now, stop the “Queue Reader Agent”, if it is setup in your environment.

Snapshot Agent should be completed and stop as well. Generally it supposed to be stopped only so I have not included its steps here.

Anyway, let us move on and take database “AdventureWorks2012” offline with following commands:

 USE master
 GO

-- Making database with restricted user and offline
 ALTER DATABASE AdventureWorks2012 SET restricted_user with rollback immediate;
 ALTER DATABASE AdventureWorks2012 SET OFFLINE;

I have my data/log file at “C:\AdventureWorks” folder and I want to move it to “D:\AdventureWorks” folder so I execute following command.

 USE master
 GO

-- Mapping database with new file path
 ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_data , FILENAME = 'D:\AdventureWorks\AdventureWorks2012_data.mdf');
 ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_log , FILENAME = 'D:\AdventureWorks\AdventureWorks2012_log.ldf');
 

Now, we have changed the path in system catalog but physically my files are still there in “C:\AdventureWorks” folder so I will manually copy it from current location and paste it in new location in “D” drive.

Once I finish manual copy of database files, I will execute following command to start database back online.

 USE master
 GO

-- Making database online and make available with multiuser
 ALTER DATABASE AdventureWorks2012 SET ONLINE;
 ALTER DATABASE  AdventureWorks2012 SET multi_user;
 

Now, let us confirm that whether our files are being read from new location or not by executing following command:

 SELECT * FROM sys.sysaltfiles
 WHERE DB_NAME(dbid) = 'AdventureWorks2012'
 GO
 

Yes, finally I have moved database file:

Now, start your all agents which you have stopped from replication monitor and you are done.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

One thought on “Move replicated database file to new location in SQL Server 2012

Comments are closed.