Can a published SQL Server database be renamed

By:   |   Updated: 2012-03-15   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | More > Replication


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.


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.

Error on renaming 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.

Run sp_helpdb on publication database

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


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.

Run sp_helpdb on publication database after changing logical file name

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')

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

Enter name of new physical file

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)


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)


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

Error if you had not renamed the physical file at OS level

 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

Last Updated: 2012-03-15

get scripts

next tip button

About the author

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Monday, July 30, 2012 - 5:26:58 AM - Pritesh Back To Top

Good Job moin....:-) very informative and smartly writtened.

Thursday, April 12, 2012 - 4:16:26 PM - sam Back To Top

Very misleading article. Please change header. This article is only changing physical file names, not database name. BEWARE!!

Wednesday, March 21, 2012 - 10:50:40 PM - bojanna mk Back To Top

Thank you Moinu.Interesting and informative!!!

Wednesday, March 21, 2012 - 6:55:42 AM - Srinath Back To Top

Oh handsomely written...Very informative..

Saturday, March 17, 2012 - 2:44:25 AM - Mohammed Moinudheen Back To Top

Hi Jeff,

Thanks for your comments. The title has been edited now.

Mohammed Moinudheen




Thursday, March 15, 2012 - 4:46:17 PM - jeff Back To Top

Sorry, the title is misleading as you did not rename the publication db name anyway.


Recommended Reading

How to rename a SQL Server database

Renaming SQL Server database objects and changing object owners

Steps to Rename a SQL Server Database

Steps to Rename a Subscriber Database for SQL Server Transactional Replication

Steps to change the server name for a SQL Server machine

get free sql tips
agree to terms

Learn more about SQL Server tools