By: Mohammed Moinudheen | Comments (6) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | > Replication
Problem
I have 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. In an earlier tip related to renaming the subscription database you encountered no error message when you used a similar script.
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):
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. Refer to this link for details.
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.
Next Steps
- Get familiar with the replication concepts and the related system tables and procedures
- Try testing this tip in your own replication setup where transactional replication is configured
- Refer to this tip which describes how database files of a replicated SQL Server database could be moved without affecting replication
- Refer to other related replication tips
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
