{"id":23120,"date":"2023-03-01T22:39:38","date_gmt":"2023-03-01T21:39:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=23120"},"modified":"2023-03-03T21:20:00","modified_gmt":"2023-03-03T20:20:00","slug":"move-secondary-database-files-in-sql-server-ag-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/","title":{"rendered":"Move secondary database files in SQL Server AG with PowerShell"},"content":{"rendered":"\n<p>For some circumstances, you may want to move data and log files for a database to a different location.<br>Usually, this operation has to be made offline. But with an Availability Group (AG) environment, it is possible to choose an approach which is slightly transparent.<\/p>\n\n\n\n<p>If you have a lot of databases to process, you may want to automate this operation: PowerShell is quite convenient for that.<br>In this blog, I will present you how I did using <a href=\"https:\/\/dbatools.io\/\">dbatools <\/a>module.<br><\/p>\n\n\n\n<p>In the following script, I took the primary as reference for my restore: this is just as an example.<br>Of course, you can customize the target folders based on your needs.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># IMPORTANT: dbatools needs to be installed on the server where running this script\n\n# Configure the following parameters\n$Primary = '&lt;MyPrimary&gt;';\n$Secondary = '&lt;MySecondary&gt;';\n$Database = '&lt;MyDatabase&gt;';\n$AvailabilityGroup = '&lt;MyAG&gt;';\n\n$SharedBackup = '&lt;MyShare&gt;';\n\n\nTry\n{\n    Write-Output \"Importing module dbatools\";\n    Import-Module -Name dbatools;\n    Write-Output '...ok';\n    \n    $DatabaseFilesQuery =  \"\n    SELECT SERVERPROPERTY('ServerName') as Server_Instance,\n    '$($Database)' as Database_Name,\n    name as Logical_Name,\n    physical_name as Physical_Name, \n    type_desc as Type_Description,\n    size\/128 as Size_MB\n    FROM sys.master_files \n    WHERE database_id = DB_ID('$($Database)')\";\n\n    Write-Output \"Retrieving information about current database files\";\n    $dbFiles = Invoke-DbaQuery -SqlInstance $Secondary -Database 'master' -Query $DatabaseFilesQuery -EnableException;\n\n    $dbFiles | Format-Table Server_Instance, Database_Name, Physical_Name, Type_Description, Size_MB;\n    Write-Output '...ok';\n\n    # Retrieve locations from primary\n    Write-Output \"Retrieving information about new database folders\";\n    \n    $Files = Get-DbaDbFile -SqlInstance $Primary -Database $Database;\n\n    # Locate all data files based on primary data file\n    $NewDataFolder = Split-Path (($Files | Where-Object { ($_.Type -eq 0) -and ($_.ID -eq 1) -and ($_.FileGroupName -eq 'PRIMARY')}).PhysicalName);\n\t\n\t# Assume there is only one log file\n    $NewLogFolder = Split-Path (($Files | Where-Object { ($_.Type -eq 1) -and ($_.ID -eq 2)}).PhysicalName)\n    Write-Output '...ok';\n    \n\n\t$title   = 'Removing secondary database from old folers'\n\t$msg     = \"Do you want to Move database file(s) to  $($NewDataFolder) and to $($NewLogFolder) on Secondary $($Secondary)?\"\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 \"Aborted by user...\";\n\t\tReturn;\n    }\n\n\n    # Create subfolder to database\n    Write-Output \"Creating new database folders\";\n    $CreateSubFoldersQuery = \"\n    EXECUTE &#091;master].dbo.xp_create_subdir '$($NewDataFolder)';\n    EXECUTE &#091;master].dbo.xp_create_subdir '$($NewLogFolder)';\n    \"\n    $Null = Invoke-DbaQuery -SqlInstance $Secondary -Database 'master' -Query $CreateSubFoldersQuery -EnableException;\n    Write-Output '...ok';\n\n    # Remove database from AG on secondary\n    Write-Output \"Removing database $($Database) from Secondary $($Secondary)\";\n    $RemoveDBSecondaryQuery = \"\n    ALTER DATABASE &#091;$($Database)] SET HADR OFF;\n    \";\n    $Null = Invoke-DbaQuery -SqlInstance $Secondary -Database 'master' -Query $RemoveDBSecondaryQuery -EnableException;\n    Write-Output '...ok';\n\n    # Backup Source database - Copy Only option is configurable with this command\n    Write-Output \"Performing full backup of database $($Database) to Shared Backup $($SharedBackup)\";\n    $FullBackups = Backup-DbaDatabase -SqlInstance $Primary -Database $Database -Path $SharedBackup -Type Full -FileCount 4;\n    Write-Output '...ok';\n\n    Write-Output \"Performing log backup of database $($Database) to Shared Backup $($SharedBackup)\";\n    $LogBackup = Backup-DbaDatabase -SqlInstance $Primary -Database $Database -Path $SharedBackup -Type Log;\n    Write-Output '...ok';\n\n    # Restore to secondary with moving files\n    Write-Output \"Restoring FULL backup for database $($Database) on Secondary $($Secondary)\";\n    Restore-DbaDatabase -Path $FullBackups -EnableException -SqlInstance $Secondary -WithReplace -NoRecovery -DestinationDataDirectory \"$($NewDataFolder)\" -DestinationLogDirectory \"$($NewLogFolder)\";\n    Write-Output '...ok';\n\n    Write-Output \"Restoring LOG backup for database $($Database) on Secondary $($Secondary)\";\n    Restore-DbaDatabase -Path $LogBackup -EnableException -SqlInstance $Secondary -NoRecovery -Continue;\n    Write-Output '...ok';\n\n    # Add database AG to secondary\n    Write-Output \"Adding database $($Database) on Secondary $($Secondary)\";\n    $AddDBSecondaryQuery = \"\n    ALTER DATABASE &#091;$($Database)] SET HADR AVAILABILITY GROUP = $($AvailabilityGroup);\n    \";\n    $Null = Invoke-DbaQuery -SqlInstance $Secondary -Database 'master' -Query $AddDBSecondaryQuery -EnableException;\n    Write-Output '...ok';\n\n    # Clean up backup files\n    Write-Output \"Cleaning full backup files\";\n    Remove-Item -Path $FullBackups.FullName;\n    Write-Output '...ok';\n\n}\nCatch\n{\n\tWrite-Error $_.Exception.toString();\n}<\/code><\/pre>\n\n\n\n<p>The script includes a TRY\/CATCH block to stop at any unattended error: it offers a safe way to avoid any side effects.<br><br>Enjoy automation!<br><\/p>\n","protected":false},"excerpt":{"rendered":"<p>For some circumstances, you may want to move data and log files for a database to a different location.Usually, this operation has to be made offline. But with an Availability Group (AG) environment, it is possible to choose an approach which is slightly transparent. If you have a lot of databases to process, you may [&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-23120","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.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Move secondary database files in SQL Server AG with PowerShell - dbi Blog<\/title>\n<meta name=\"description\" content=\"This blog will show you how to automate the move database files for secondary databases in PowerShell (SQL Server AG).\" \/>\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-secondary-database-files-in-sql-server-ag-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Move secondary database files in SQL Server AG with PowerShell\" \/>\n<meta property=\"og:description\" content=\"This blog will show you how to automate the move database files for secondary databases in PowerShell (SQL Server AG).\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-01T21:39:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-03-03T20:20:00+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-secondary-database-files-in-sql-server-ag-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Move secondary database files in SQL Server AG with PowerShell\",\"datePublished\":\"2023-03-01T21:39:38+00:00\",\"dateModified\":\"2023-03-03T20:20:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\"},\"wordCount\":146,\"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-secondary-database-files-in-sql-server-ag-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\",\"name\":\"Move secondary database files in SQL Server AG with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-03-01T21:39:38+00:00\",\"dateModified\":\"2023-03-03T20:20:00+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 for secondary databases in PowerShell (SQL Server AG).\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Move secondary database files in SQL Server AG 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 secondary database files in SQL Server AG with PowerShell - dbi Blog","description":"This blog will show you how to automate the move database files for secondary databases in PowerShell (SQL Server AG).","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-secondary-database-files-in-sql-server-ag-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Move secondary database files in SQL Server AG with PowerShell","og_description":"This blog will show you how to automate the move database files for secondary databases in PowerShell (SQL Server AG).","og_url":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2023-03-01T21:39:38+00:00","article_modified_time":"2023-03-03T20:20:00+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-secondary-database-files-in-sql-server-ag-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Move secondary database files in SQL Server AG with PowerShell","datePublished":"2023-03-01T21:39:38+00:00","dateModified":"2023-03-03T20:20:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/"},"wordCount":146,"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-secondary-database-files-in-sql-server-ag-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/","name":"Move secondary database files in SQL Server AG with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-03-01T21:39:38+00:00","dateModified":"2023-03-03T20:20:00+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 for secondary databases in PowerShell (SQL Server AG).","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/move-secondary-database-files-in-sql-server-ag-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Move secondary database files in SQL Server AG 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\/23120","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=23120"}],"version-history":[{"count":8,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/23120\/revisions"}],"predecessor-version":[{"id":23219,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/23120\/revisions\/23219"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=23120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=23120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=23120"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=23120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}