{"id":1179,"date":"2007-09-28T15:27:44","date_gmt":"2007-09-28T15:27:44","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-2-Moving-a-database-while-Database-Mirroring-is-running.aspx"},"modified":"2017-04-13T09:52:06","modified_gmt":"2017-04-13T16:52:06","slug":"search-engine-qa-2-moving-a-database-while-database-mirroring-is-running","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/","title":{"rendered":"Search Engine Q&A #2: Moving a database while Database Mirroring is running"},"content":{"rendered":"

This was a question from the MSDN Disaster Recovery forum<\/a> I started while I was at Microsoft.<\/p>\n

I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the principle and mirror to the SAN with no down time? or minimal down time?<\/em><\/p>\n

As far as I know, there isn’t any such document so I had a crack at coming up with a list of operations. Here’s what I had:<\/p>\n

    \n
  1. Take a full backup of the principal on node A<\/li>\n
  2. Restore it on the SAN on node B\u00a0using WITH\u00a0NORECOVERY, remembering to use WITH MOVE to place the files correctly, and with a different database name than the current mirror<\/li>\n
  3. Take the required log backup on the principal and restore on the database copy on the SAN on node B<\/li>\n
  4. Break the mirroring partnership<\/li>\n
  5. Drop the current mirror database on node B<\/li>\n
  6. Rename the database on the SAN on node B to be the mirror database — THIS DOESN”T WORK!<\/li>\n
  7. Setup the mirroring partnership to point to the newly restored database on the SAN on node B<\/li>\n
  8. Start mirroring and the new mirror will catch-up<\/li>\n
  9. Failover to the mirror on node B, which becomes the new principal<\/li>\n
  10. Follow the same procedure to move the new mirror on node A onto its SAN<\/li>\n
  11. Failback if you want to<\/li>\n<\/ol>\n

    And I promised to try it out to make sure I had it right so in this blog post I’m going to walk through the steps of doing this. It turns out that the steps above are slightly incorrect. Step 6 above doesn’t work because the database is in recovery (so is inaccessible) and there’s a short-cut when moving the database on the first node to avoid having to take and copy more backups. Let’s see how it works and I’ll post the corrected sequence at the end.<\/p>\n

    As I did in yesterday’s mirroring post, I’m going to use the TicketSalesDB database from our Always-On DVDs. It’s only a few hundred MB instead of 600GB but the principal is the same (no pun intended :-)). I’ve got mirroring running between two nodes, SQLDEV01 (the principal) and SQLDEV02 (the mirror), both of which are running 2005 SP2 and I’ve got a simulated workload inserting rows into the database. I don’t actually have a SAN laying around so I’m cheating and I have directories called C:\\SQLDEV01SAN and C:\\SQLDEV02SAN instead. It’s the location change that’s the interesting part, not where the actual location is.<\/p>\n

    Step 1<\/strong><\/p>\n

    On SQLDEV01, take a full backup and a log backup:<\/p>\n

    \r\nBACKUP DATABASE [TicketSalesDB] TO DISK = N'C:\\SQLskills\\TicketSalesDB.BAK' WITH INIT;\r\nGO\r\n\r\nBACKUP LOG [TicketSalesDB] TO DISK = N'C:\\SQLskills\\TicketSalesDB_Log.bak' WITH INIT;\r\nGO\r\n<\/pre>\n
    Step 2<\/strong><\/p>\n
    On SQLDEV01, break the mirroring partnership:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER OFF;\r\nGO\r\n<\/pre>\n
    And just check that it’s gone:<\/p>\n
    \r\nSELECT [mirroring_state_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');\r\nGO\r\n<\/pre>\n
    \r\nNULL\r\n<\/pre>\n
    Step 3<\/strong><\/p>\n
    On SQLDEV02, drop the mirror database – this wouldn’t work unless mirroring was no longer running:<\/p>\n
    \r\nDROP DATABASE [TicketSalesDB];\r\nGO\r\n<\/pre>\n
    Step 4<\/strong><\/p>\n
    Copy the backups to SQLDEV02 and restore\u00a0them on the SAN and remembering to use WITH NORECOVERY<\/em>:<\/p>\n
    \r\nRESTORE DATABASE [TicketSalesDB] FROM DISK = N'C:\\SQLskills\\TicketSalesDB.bak'\r\nWITH\r\n    MOVE N'TicketSalesDBData' TO N'C:\\SQLDEV02SAN\\TicketSalesDBData.MDF',\r\n    MOVE N'TicketSalesFG2005Q1' TO N'C:\\SQLDEV02SAN\\TicketSalesFG2005Q1.NDF',\r\n    MOVE N'TicketSalesFG2005Q2' TO N'C:\\SQLDEV02SAN\\TicketSalesFG2005Q2.NDF',\r\n    MOVE N'TicketSalesFG2005Q3' TO N'C:\\SQLDEV02SAN\\TicketSalesFG2005Q3.NDF',\r\n    MOVE N'TicketSalesFG2005Q4' TO N'C:\\SQLDEV02SAN\\TicketSalesFG2005Q4.NDF',\r\n    MOVE N'TicketSalesDBLog' TO N'C:\\SQLDEV02SAN\\TicketSalesDBLog.LDF',\r\n    NORECOVERY;\r\nGO\r\n<\/pre>\n
    Step 5<\/strong><\/p>\n
    On SQLDEV02, set the mirroring partner to be SQLDEV01:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP:\/\/SQLDEV01:5091';\r\nGO\r\n<\/pre>\n
    Step 6<\/strong><\/p>\n
    On SQLDEV01, start mirroring:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP:\/\/SQLDEV02:5092';\r\nGO\r\n<\/pre>\n
    And check that it’s running:<\/p>\n
    \r\nSELECT [mirroring_state_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');\r\nGO\r\n<\/pre>\n
    This time it returns:<\/p>\n
    \r\nSYNCHRONIZED\r\n<\/pre>\n
    Step 7<\/strong><\/p>\n
    Now we need to failover so that we can move the database on SQLDEV01 onto its SAN. Before we do that, let’s make sure that SQLDEV01 is the principal:<\/p>\n
    \r\nSELECT [mirroring_role_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');\r\nGO\r\n<\/pre>\n
    which returns:<\/p>\n
    \r\nPRINCIPAL\r\n<\/pre>\n
    Now force the failover:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER FAILOVER;\r\nGO\r\n<\/pre>\n
    And query the DMV again to make sure. This time the mirroring_state_desc returned is:<\/p>\n
    \r\nMIRROR\r\n<\/pre>\n
    Excellent!<\/p>\n
    Now, I did all of this while my workload was running and it automatically failed over to SQLDEV02, with the database now hosted on the SAN. To do the same move on SQLDEV01, we don’t need to go through the backup and copy process again – we can just use the original backups we took in step 1.<\/p>\n
    Step 8<\/strong><\/p>\n
    We need to break the mirroring partnership again, this time executing on SQLDEV02, the new principal:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER OFF;\r\nGO\r\n<\/pre>\n
    On SQLDEV01, we can now drop the database and restore the original backups onto the SAN:<\/p>\n
    \r\nDROP DATABASE [TicketSalesDB];\r\nGO\r\n\r\nRESTORE DATABASE [TicketSalesDB] FROM DISK = N'C:\\SQLskills\\TicketSalesDB.bak'\r\nWITH\r\n    MOVE N'TicketSalesDBData' TO N'C:\\SQLDEV01SAN\\TicketSalesDBData.MDF',\r\n    MOVE N'TicketSalesFG2005Q1' TO N'C:\\SQLDEV01SAN\\TicketSalesFG2005Q1.NDF',\r\n    MOVE N'TicketSalesFG2005Q2' TO N'C:\\SQLDEV01SAN\\TicketSalesFG2005Q2.NDF',\r\n    MOVE N'TicketSalesFG2005Q3' TO N'C:\\SQLDEV01SAN\\TicketSalesFG2005Q3.NDF',\r\n    MOVE N'TicketSalesFG2005Q4' TO N'C:\\SQLDEV01SAN\\TicketSalesFG2005Q4.NDF',\r\n    MOVE N'TicketSalesDBLog' TO N'C:\\SQLDEV01SAN\\TicketSalesDBLog.LDF',\r\n    NORECOVERY;\r\nGO\r\n\r\nRESTORE LOG [TicketSalesDB] FROM DISK = N'C:\\SQLskills\\TicketSalesDB_Log.bak' WITH NORECOVERY;\r\nGO\r\n<\/pre>\n
    And setup mirroring again. On SQLDEV01:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP:\/\/SQLDEV02:5092';\r\nGO\r\n<\/pre>\n
    And on SQLDEV02:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP:\/\/SQLDEV01:5091';\r\nGO\r\n<\/pre>\n
    And we’re running again.<\/p>\n
    Step 9<\/strong><\/p>\n
    Now all we need to do is fail the workload back to SQLDEV01 by executing this on SQLDEV02:<\/p>\n
    \r\nALTER DATABASE [TicketSalesDB] SET PARTNER FAILOVER;\r\nGO\r\n<\/pre>\n
    Summary<\/strong><\/p>\n
    So – the corrected sequence for moving a database while mirroring is running is the following:<\/p>\n
     <\/p>\n
      \n
    1. Take a full backup of the principal database on node A, and the required log backup<\/li>\n
    2. Break the mirroring partnership<\/li>\n
    3. Drop the current mirror database on node B<\/li>\n
    4. Copy the backups to node B and restore it on the SAN on node B\u00a0using WITH\u00a0NORECOVERY, remembering to use WITH MOVE to place the files correctly<\/li>\n
    5. Setup the mirroring partnership to point to the newly restored database on the SAN on node B<\/li>\n
    6. Start mirroring and the new mirror will catch-up<\/li>\n
    7. Failover to the mirror on node B, which becomes the new principal<\/li>\n
    8. Follow the same procedure to move the new mirror on node A onto its SAN, but using the original backups from step 1<\/li>\n
    9. Failback<\/li>\n<\/ol>\n
      Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"
      This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,32,38,78],"tags":[],"class_list":["post-1179","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-database-mirroring","category-example-scripts","category-search-engine-q-and-a"],"yoast_head":"\nSearch Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-09-28T15:27:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:52:06+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/\",\"name\":\"Search Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-09-28T15:27:44+00:00\",\"dateModified\":\"2017-04-13T16:52:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&A #2: Moving a database while Database Mirroring is running\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Search Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal","og_description":"This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/","og_site_name":"Paul S. Randal","article_published_time":"2007-09-28T15:27:44+00:00","article_modified_time":"2017-04-13T16:52:06+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/","name":"Search Engine Q&A #2: Moving a database while Database Mirroring is running - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-09-28T15:27:44+00:00","dateModified":"2017-04-13T16:52:06+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&A #2: Moving a database while Database Mirroring is running"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1179"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}