{"id":23218,"date":"2023-03-03T21:52:32","date_gmt":"2023-03-03T20:52:32","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=23218"},"modified":"2023-03-03T21:52:33","modified_gmt":"2023-03-03T20:52:33","slug":"move-sql-server-database-files-to-different-folders-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/","title":{"rendered":"Move SQL Server database files to different folders with PowerShell"},"content":{"rendered":"\n<p>In my previous <a href=\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\">post<\/a>, I explained how to move SQL Server databases files programmatically by leveraging the high availability of an Availability Group (AG).<br>But when you can afford to have a database unavailable for a short time, and if it is of a reasonable size, then an offline approach may be desirable. This will have the advantage of also working for standalone databases, but also of not needing to reapply the manipulation on the primary after a fail-over to a secondary.<\/p>\n\n\n\n<p><br>In this blog, I will again use <a href=\"https:\/\/dbatools.io\/\">dbatools <\/a>module in my PowerShell script. For automation in SQL Server, this is now a must-have language.<\/p>\n\n\n\n<p>In the following script, it needs to be run on the Primary server because I am using local paths to move database files.<br>For a remote approach, you can either use PowerShell remote sessions or remote paths to achieve this goal.<\/p>\n\n\n\n<p>The script will also remove the database from AG during the detach\/attach operation, and will add back the database in the AG with the synchronization.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># IMPORTANT: this script has to be run on the Primary only\n# dbatools module is required\n\n$Primary = '&lt;MyPrimary&gt;';\n$Secondary = '&lt;MySecondary&gt;';\n\n$Database = '&lt;MyDatabase&gt;';\n$AvailabilityGroup = '&lt;MyAG&gt;';\n\n$NewDataFolder = '&lt;MyNewDataFolder&gt;';\n$NewLogsFolder = '&lt;MyNewLogFolder&gt;';\n\n\nTry\n{\n\tImport-Module -Name dbatools;\n\t\n\t$dbFiles = Get-DbaDbFile -SqlInstance $Primary -Database $Database;\n\t$dbFiles | Format-Table ComputerName, InstanceName, Database, PhysicalName, LogicalName, TypeDescription, Size;\n\t\n\t$title   = 'Moving database to another folder (Offline)'\n\t$msg     = \"Do you want to Move data file(s) to $NewDataFolder and logs file to $NewLogsFolder ?\"\n\t$options = '&amp;Yes', '&amp;No'\n\t$default = 1  # 0=Yes, 1=No\n\n\t$Continue = $False;\n\t\n\tdo {\n\t\t$response = $Host.UI.PromptForChoice($title, $msg, $options, $default)\n\t\tif ($response -eq 0) {\n\t\t\t$Continue = $True;\n\t\t\t$response = 1;\n\t\t}\n\t} until ($response -eq 1)\n\t\n\tIf (-not $Continue){\n\t\tWrite-Warning -Message \"Aborted by user...\";\n\t\tReturn;\n\t}\n\t\n\t# Test path New data folder\n\tWrite-Output -Message \"Testing New Data folder $NewDataFolder\";\n\tIf (-not (Test-Path -Path $NewDataFolder)){\n\t\tWrite-Warning -Message \"Target data folder $NewDataFolder does not exist. Aborting...\";\n\t\tReturn;\n\t};\n    Write-Output '...ok';\n\t\n\t# Test path New logs folder\n\tWrite-Output -Message \"Testing New Logs folder $NewLogsFolder\";\n\tIf (-not (Test-Path -Path $NewLogsFolder)){\n\t\tWrite-Warning -Message \"Target logs folder $NewLogsFolder does not exist. Aborting...\";\n\t\tReturn;\n\t};\n    Write-Output '...ok';\n\t\n\t# Check if AG is configured\n\tIf ($AvailabilityGroup){\n\t\t# Remove database from AG\n\t\tWrite-Output \"Start removing database $Database from AG $AvailabilityGroup on instance $Primary\";\n\t\tRemove-DbaAgDatabase -SqlInstance $Primary -Database $Database -AvailabilityGroup $AvailabilityGroup -EnableException;\n        Write-Output '...ok';\n\t}\n\n\t# Detach database on Primary\n\tWrite-Output \"Detaching database $Database on instance $Primary\";\n\tDismount-DbaDatabase -SqlInstance $Primary -Database $Database -EnableException;\n    Write-Output '...ok';\n\n\t\n\t# Move data files\n\tWrite-Output \"Moving data file(s) of $Database on instance $Primary\";\n\t($dbFiles | Where-Object Type -eq 0).PhysicalName | ForEach-Object -Process {Move-Item -Path $_ -Destination $NewDataFolder;}\n    Write-Output '...ok';\n\t\n\t# Move logs files\n    Write-Output \"Moving logs file of database $Database on instance $Primary\";\n\t($dbFiles | Where-Object Type -eq 1).PhysicalName | ForEach-Object -Process {Move-Item -Path $_ -Destination $NewLogsFolder;}\n    Write-Output '...ok';\n\t\n\t# Create new file structure for database to attach\n\t$newdbFiles = @(Get-ChildItem $NewDataFolder) + @(Get-ChildItem $NewLogsFolder);\n\t$fs = New-Object System.Collections.Specialized.StringCollection; \n\t@($newdbFiles) | ForEach-Object -Process { `\n\t\t$Null = $fs.Add($_.FullName); `\t\n\t}; \n\t\n\t# Attach database on Primary\n    Write-Output \"Attaching database $database on instance $Primary\"\n\tMount-DbaDatabase -SqlInstance $Primary -Database $Database -FileStructure $fs;\n    Write-Output \"...ok\";\n\t\n\n\t# Add database to AG\n\tIf ($Secondary -and $AvailabilityGroup){\n\t\t\n        Write-Output \"Removing database $database on secondary $Secondary\"; \n\t\tRemove-dbaDatabase -SqlInstance $Secondary -Database $Database;\n        Write-Output \"...ok\";\n\t\t\n        Write-Output \"Start adding database $Database from AG $AvailabilityGroup on instance $Primary to secondary $Secondary\";\n\t\tAdd-DbaAgDatabase -SqlInstance $Primary -Database $Database -AvailabilityGroup $AvailabilityGroup -Secondary $Secondary -SeedingMode Automatic;\n        Write-Output \"...ok\";\n\t}\n\n}\nCatch\n{\n\tWrite-Error $_.Exception.toString();\n}<\/code><\/pre>\n\n\n\n<p>Running this script on a production environment, I have seen a few seconds of downtime in the worst case with &lt;500GB databases.<br>But this will depend mainly on your infrastructure and whether the database files are transferred to another physical drive. Another thing to consider is the database cache will be reset with this approach.<br><br>Enjoy automation!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my previous post, I explained how to move SQL Server databases files programmatically by leveraging the high availability of an Availability Group (AG).But when you can afford to have a database unavailable for a short time, and if it is of a reasonable size, then an offline approach may be desirable. This will have [&hellip;]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[99],"tags":[466,272,51],"type_dbi":[],"class_list":["post-23218","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-alwayson","tag-powershell","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Move SQL Server database files to different folders with PowerShell - dbi Blog<\/title>\n<meta name=\"description\" content=\"This blog will show you how to automate the move database files in PowerShell with a detach\/attach approach..\" \/>\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.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Move SQL Server database files to different folders with PowerShell\" \/>\n<meta property=\"og:description\" content=\"This blog will show you how to automate the move database files in PowerShell with a detach\/attach approach..\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-03T20:52:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-03-03T20:52:33+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Move SQL Server database files to different folders with PowerShell\",\"datePublished\":\"2023-03-03T20:52:32+00:00\",\"dateModified\":\"2023-03-03T20:52:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/\"},\"wordCount\":241,\"commentCount\":0,\"keywords\":[\"AlwaysOn\",\"PowerShell\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/\",\"name\":\"Move SQL Server database files to different folders with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-03-03T20:52:32+00:00\",\"dateModified\":\"2023-03-03T20:52:33+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\"},\"description\":\"This blog will show you how to automate the move database files in PowerShell with a detach\\\/attach approach..\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/move-sql-server-database-files-to-different-folders-with-powershell\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Move SQL Server database files to different folders with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \\\/ Banking, etc.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/nathan-courtine\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Move SQL Server database files to different folders with PowerShell - dbi Blog","description":"This blog will show you how to automate the move database files in PowerShell with a detach\/attach approach..","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.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Move SQL Server database files to different folders with PowerShell","og_description":"This blog will show you how to automate the move database files in PowerShell with a detach\/attach approach..","og_url":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2023-03-03T20:52:32+00:00","article_modified_time":"2023-03-03T20:52:33+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Move SQL Server database files to different folders with PowerShell","datePublished":"2023-03-03T20:52:32+00:00","dateModified":"2023-03-03T20:52:33+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/"},"wordCount":241,"commentCount":0,"keywords":["AlwaysOn","PowerShell","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/","name":"Move SQL Server database files to different folders with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-03-03T20:52:32+00:00","dateModified":"2023-03-03T20:52:33+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"description":"This blog will show you how to automate the move database files in PowerShell with a detach\/attach approach..","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/move-sql-server-database-files-to-different-folders-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Move SQL Server database files to different folders with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/23218","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=23218"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/23218\/revisions"}],"predecessor-version":[{"id":23221,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/23218\/revisions\/23221"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=23218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=23218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=23218"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=23218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}