Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Can a published SQL Server database be renamed

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (6)   |   Related Tips: More > 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.

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.

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.

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

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)

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.

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 Update: 3/15/2012


About the author
MSSQLTips author Mohammed Moinudheen
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, March 15, 2012 - 4:46:17 PM - jeff Read The Tip

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


Saturday, March 17, 2012 - 2:44:25 AM - Mohammed Moinudheen Read The Tip

Hi Jeff,

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

Mohammed Moinudheen

 

 

 


Wednesday, March 21, 2012 - 6:55:42 AM - Srinath Read The Tip

Oh handsomely written...Very informative..


Wednesday, March 21, 2012 - 10:50:40 PM - bojanna mk Read The Tip

Thank you Moinu.Interesting and informative!!!


Thursday, April 12, 2012 - 4:16:26 PM - sam Read The Tip

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


Monday, July 30, 2012 - 5:26:58 AM - Pritesh Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.