Scenario

I have a transactional replication configured in production. I am wondering if we could rename the publication database in transactional replication without having to drop and recreate the replication set up. Also, is it possible to rename the database files of the publication database without affecting the replication configuration.

Solution

Let’s look at three different scenarios for renaming a database:

• Renaming the Publication Database
• Renaming the Publication Database Logical File Names
• Renaming the Publication Database Physical Files

Renaming the Publication Database

Let’s first try renaming a publication database in a test replication setup. This tip assumes transactional replication is already configured.

Use the sample script below for renaming your publication database.

USE master;

ALTER DATABASE Current_Publication_database_name

  Modify Name = New_publication_database_name;

Once you run this script on your publication database you will encounter an error message as shown below which prevents you from renaming the publication database.

As evident from the error message, we would need to drop the publications, rename the database and re-configure replication all over again.  So there is no easy way to do this.

Renaming the Publication Database Logical File Names

Follow these steps:

1) Run sp_helpdb on your publication database to get the logical file name as shown below.

2) Assuming, you need to rename logical name REP_P to REP_P_NEW_DB, execute the below script on the publication database.

ALTER DATABASE REP_P 

MODIFY FILE (NAME = 'REP_P', NEWNAME= 'REP_P_NEW_DB')

You would see this message after running this step: The file name ‘REP_P_NEW_DB’ has been set.

3) Run sp_helpdb again to verify that the file name has been changed. You will notice the logical name has changed and the physical file name has not changed.

4) If you check Replication Monitor you can confirm that these steps had no impact on replication and everything is still working.

Renaming the Publication Database Physical Files

Follow these steps:

1) Run sp_helpdb on your publication database to get the actual file name and location.

2) Execute below script on your publication database (substituting your database name and details):

ALTER DATABASE Publication_DB_Name
MODIFY FILE (NAME =Logical_Name,
FILENAME = ‘Take this path from sp_helpdb\ENTER_NEW_FILE_NAME .mdf’)
GO

When running this script, ensure to rename the physical file name as shown below:

From the screenshot, you could see that the system catalog has been updated with the new file name. However, we would need to rename this data file at the OS level for this to really take effect. If you navigate to the actual file path, you will see the data file still has the old physical name which needs to be renamed.

3) Stop the Log Reader Agent job as it would be connected to the publication database

4) Take the publication database offline using the below command (substituting your database name)

ALTER DATABASE Publication_DB SET OFFLINE

5) Go to the file location, as specified in step(2) and rename the old data file name to the new one name mentioned in step(2). If you do not have proper privileges to log on to the box, this step could be achieved using xp_cmdshell as well.

6) Bring the publication database online using the below command (substituting your database name)

ALTER DATABASE Publication_DB SET ONLINE

If you skipped step(5), you will encounter the below error message when bringing the database online.

7) Start the Log Reader Agent job and from Replication Monitor you can confirm that replication is running without issue.

The above steps were performed using SQL Server 2008 R2. From this tip, we could see that the logical and physical file name of a publication database could be renamed without affecting the replication configuration. However, renaming the publication database itself would require you to configure replication all over again after removing replication. Also, in this tip examples of renaming logical\physical file names of data files were used, the same is applicable for log files for the publication database as well.

 

Thanks for reading this article,

Next steps :

1. Add this script to your database toolkit
2. Share this with your colleagues because Sharing is Learning
3. Comment below if you need any assistance

Powered by CodeReview – Let’s make it Better!