{"id":866,"date":"2015-07-03T20:44:56","date_gmt":"2015-07-03T14:44:56","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=866"},"modified":"2015-07-03T22:49:30","modified_gmt":"2015-07-03T16:49:30","slug":"rename-a-published-sql-server-database","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/","title":{"rendered":"Rename a published SQL Server database"},"content":{"rendered":"<h3><strong>Scenario<\/strong><\/h3>\n<p>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.<\/p>\n<h3><strong>Solution<\/strong><\/h3>\n<p>Let&#8217;s look at three different scenarios for renaming a database:<\/p>\n<ul>\n<li>Renaming the Publication Database<\/li>\n<li>Renaming the Publication Database Logical File Names<\/li>\n<li>Renaming the Publication Database Physical Files<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<h3><strong>Renaming the Publication Database<\/strong><\/h3>\n<p>Let&#8217;s first try renaming a publication database in a test replication setup. This tip assumes transactional replication is already configured.<\/p>\n<p>Use the sample script below for renaming your publication database.<\/p>\n<pre>USE master;\r\n\r\nALTER DATABASE Current_Publication_database_name\r\n\r\n\u00a0 Modify Name = New_publication_database_name;<\/pre>\n<p>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.<\/p>\n<p>As evident from the error message, we would need to drop the publications, rename the database and re-configure replication all over again.\u00a0 So there is no easy way to do this.<\/p>\n<div align=\"center\">\n<hr align=\"center\" size=\"2\" width=\"100%\" \/>\n<\/div>\n<h3><strong>Renaming the Publication Database Logical File Names<\/strong><\/h3>\n<p>Follow these steps:<\/p>\n<p>1) Run sp_helpdb on your publication database to get the logical file name as shown below.<\/p>\n<p>2) Assuming, you need to rename logical name REP_P to REP_P_NEW_DB, execute the below script on the publication database.<\/p>\n<pre>ALTER DATABASE REP_P \r\n\r\nMODIFY FILE (NAME = 'REP_P', NEWNAME= 'REP_P_NEW_DB')<\/pre>\n<p>You would see this message after running this step: <em>The file name &#8216;REP_P_NEW_DB&#8217; has been set.<\/em><\/p>\n<p>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.<\/p>\n<p>4) If you check Replication Monitor you can confirm that these steps had no impact on replication and everything is still working.<\/p>\n<div align=\"center\">\n<hr align=\"center\" size=\"2\" width=\"100%\" \/>\n<\/div>\n<h3><strong>Renaming the Publication Database Physical Files<\/strong><\/h3>\n<p>Follow these steps:<\/p>\n<p>1) Run sp_helpdb on your publication database to get the actual file name and location.<\/p>\n<p>2) Execute below script on your publication database (substituting your database name and details):<\/p>\n<p>ALTER DATABASE Publication_DB_Name<br \/>\nMODIFY FILE (NAME =Logical_Name,<br \/>\nFILENAME = &#8216;Take this path from sp_helpdb\\<strong><span style=\"text-decoration: underline;\">ENTER_NEW_FILE_NAME<\/span><\/strong> .mdf&#8217;)<br \/>\nGO<\/p>\n<p>When running this script, ensure to rename the physical file name as shown below:<\/p>\n<p>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.<\/p>\n<p>3) Stop the Log Reader Agent job as it would be connected to the publication database<\/p>\n<p>4) Take the publication database offline using the below command (substituting your database name)<\/p>\n<pre>ALTER DATABASE Publication_DB SET OFFLINE<\/pre>\n<p>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.<\/p>\n<p>6) Bring the publication database online using the below command (substituting your database name)<\/p>\n<pre>ALTER DATABASE Publication_DB SET ONLINE<\/pre>\n<p>If you skipped step(5), you will encounter the below error message when bringing the database online.<\/p>\n<p>7) Start the Log Reader Agent job and from Replication Monitor you can confirm that replication is running without issue.<\/p>\n<p>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.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Thanks for reading this article,<\/strong><\/p>\n<p><strong>Next steps :<\/strong><\/p>\n<ol>\n<li><strong>Add this script to your database toolkit<\/strong><\/li>\n<li><strong>Share this with your colleagues because Sharing is Learning<\/strong><\/li>\n<li><strong>Comment below if you need any assistance<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: right;\"><em><strong>Powered by <a href=\"http:\/\/www.codereview.co\">CodeReview &#8211; Let&#8217;s make it Better!<\/a><\/strong><\/em><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-866\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-866\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-866\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-866\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-866-69d43d1c4f6d5' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=866&amp;origin=dotnetsql.info&amp;obj_id=76243027-866-69d43d1c4f6d5' data-name='like-post-frame-76243027-866-69d43d1c4f6d5'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>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&#8217;s look at three &hellip; <a href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Rename a published SQL Server database<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-866\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-866\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-866\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-866\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/rename-a-published-sql-server-database\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-866-69d43d1c4fbdb' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=866&amp;origin=dotnetsql.info&amp;obj_id=76243027-866-69d43d1c4fbdb' data-name='like-post-frame-76243027-866-69d43d1c4fbdb'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[25],"tags":[124,276,275,97,40],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/866"}],"collection":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/comments?post=866"}],"version-history":[{"count":6,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/866\/revisions"}],"predecessor-version":[{"id":960,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/866\/revisions\/960"}],"wp:attachment":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/media?parent=866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/categories?post=866"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/tags?post=866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}