{"id":16370,"date":"2021-05-25T08:43:06","date_gmt":"2021-05-25T06:43:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/"},"modified":"2021-05-25T08:43:06","modified_gmt":"2021-05-25T06:43:06","slug":"automate-restore-from-rubrik-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/","title":{"rendered":"Automate restore from Rubrik with PowerShell"},"content":{"rendered":"<p>One of our customer uses Rubrik as a s<span dir=\"ltr\">olution to recover, manage, and secure all their data. In this context, Rubrik is used to backup and restore all the SQL Server instances.<br \/>\nWe need to run restore tests in this validated environment every year. To do so we decide to create a PowerShell script to automate the restore of all user databases from a source instance (a production one) to a destination instance (a test one). Once the restore is done an integrity check has also to be performed to validate the restored databases.<br \/>\nTo perform those actions we will use a famous PowerShell module which is the <a href=\"https:\/\/dbatools.io\/\">dbatools<\/a> and also the <a href=\"https:\/\/powershell-module-for-rubrik.readthedocs.io\/en\/latest\/\">Rubrik<\/a> module.<br \/>\n<\/span><\/p>\n<p>The first step is to define the source and destination instances, and to retrieve all the user databases from the source instance via the dbatools cmdlet Get-DbaDatabase. If you want to select just one database or databases starting by a specific string you have to use the Where-Object (commented here):<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\"># source and destination instance\n$sourceInstance = 'Thor1'; \n$destInstance = 'Thor2';  \n\n# retrieve all user databases for the source instance\n$databases = Get-DbaDatabase -SqlInstance $instance -ExcludeSystem; #| Where-Object {$_.name -match '^Test'};\n<\/pre>\n<p>Once done, we need to connect to our Rubrik server, we will use its IP Address. The cmdlet Connect_Rubrik will prompt you for a user and a password for the Rubrik connection:<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\">#IP Address of the Rubrk server\n$RubrikServer = '172.168.2.2';\n\n# Rubrik connection\n$Connection = Connect-Rubrik -Server $RubrikServer;\n<\/pre>\n<p>Now that we are connected to Rubrik we will need to find the Rubrik instance ID of the destination instance. We will need this information later during the restore operation;<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\"># Find instance ID of the destination instance\n$DestInstID = Get-RubrikDatabase -ServerInstance $destInstance -Database master | select instanceId\n<\/pre>\n<p>To restore the database to the new instance we need to know the default Data and Log paths for this instance. We will get those information now:<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\"># Find default path for the instance\n$InstPath = Get-DbaDefaultPath -SqlInstance $destInstance;\n<\/pre>\n<p>We have now our source and destination instances, our list of source databases to restore and the Rubrik connection so we can loop on source databases and restore them to the destination instance.<br \/>\nThis will be done by a Foreach which don&#8217;t ask too much effort.<br \/>\nAt this stage we need to take care if the source database is part of an AlwaysOn Availability Group or not because the way to retrieve it in Rubrik will differ.<br \/>\nWe will check for that the property AvailabilityGroupName of the source database and check that the database is not a relic, it means a dropped database:<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\">#loop on all source databases\nForeach ($database in $databases)\n{\n    IF ([string]::IsNullOrEmpty($database.AvailabilityGroupName))\n    {  \n        #Find the database which is not part of an AAG\n        $db = Get-RubrikDatabase -ServerInstance $sourceInstance.name -Database $database.name | Where-Object isrelic -eq $false;\n    }\n    ELSE\n    {\n        #Find database which is part of an AAG\n        $db = Get-RubrikDatabase -AvailabilityGroupName $database.AvailabilityGroupName -Database $database.name | Where-Object isrelic -eq $false  #| Get-RubrikSnapshot -Latest\n    }\n}  \n<\/pre>\n<p>And now comes the tricky part of the restore, the creation of the new files structure which gave me some headaches&#8230;<br \/>\nIndeed our source database can have multiples Data and Log files and those files will need to be recreated in the new instance with the logical name, file name and the correct path.<br \/>\n<strong>Here a crucial point is to never change the logical name, it should be the same than the original one otherwise it&#8217;s not working.<\/strong><br \/>\nWe will need to retrieve first the database files for the source database and after construct an array of list which will build our new database files structure, in the meantime we give a new name to our destination database by adding the suffix Rest_:<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\">#Change database name\n$destDBName = 'Rest_' + $database.name;\n\n#Find database files\n$dbFiles = Get-DbaDbFile -SqlInstance $sourceInstance.name -Database $database.Name;\n\n#construct new files structure\n$targetfiles = @();\nforeach ($dbFile in $dbFiles)\n{\n    $LogicalName = $dbFile.LogicalName; ##### Logical name should be the same as original !!!!! #####\n    $newFileName = Split-Path $dbFile.PhysicalName -leaf;\n    $newFileName = 'rest_' + $newFileName;\n    IF ($dbFile.TypeDescription -eq 'ROWS')\n    { \n        $exportPath = $InstPath.Data; }\n    ELSE\n    {   \n        $exportPath = $InstPath.Log; }\n    $targetfiles += @{logicalName=$LogicalName;exportPath=$exportPath;newFilename=$newFileName}\n}\n<\/pre>\n<p>Our restore is now ready to be executed. We will here restore the source database to the latest recovery point found in Rubrik but of course another recovery date could be selected.<br \/>\nA loop which checks the status of the restore is used to have the possibility to wait the end of the current restore before doing the next one. This will give the possibility to run an integrity check for example after the restore and also to drop the database after that to let the destination instance on the same state than before this restore test.<br \/>\nTo have a better visualization of the restore progression a progress bar is also added:<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\">$Restore = Export-RubrikDatabase -id $db.id -recoveryDateTime (get-date((Get-RubrikDatabase -id $db.id).latestrecoverypoint)) -targetInstanceId $DestInstID.instanceId -targetDatabaseName $destDBName -TargetFilePaths $targetfiles -maxDataStreams 1 -FinishRecovery\n            \n#Loop until restore is finished\nDo\n{\n    #Search status of the restore\n    $status = (Get-RubrikRequest -id $Restore.ID -Type 'mssql'); \n\n    #show a progress bar for the restore\n    Write-Progress -Activity Restoring -Status $status.status -PercentComplete $status.progress -CurrentOperation \"Restore database $database\"\n} Until ($status.progress -ge 100);\n\n#Restore done for the database\nWrite-Output \"Restore is complete on database $destDBNam\";\n<\/pre>\n<p>In this blog post I explained how to automate the restoration of multiples databases via Rubrik. I deliberately removed from my script the logging part as well as the integrity check after the restore followed by the dropped of the database. I will create another post for those parts.<br \/>\nI hope this post will help and I wish you an happy scripting!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of our customer uses Rubrik as a solution to recover, manage, and secure all their data. In this context, Rubrik is used to backup and restore all the SQL Server instances. We need to run restore tests in this validated environment every year. To do so we decide to create a PowerShell script to [&hellip;]<\/p>\n","protected":false},"author":21,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[202,272,854,2341,51],"type_dbi":[],"class_list":["post-16370","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-backup","tag-powershell","tag-restore","tag-rubrik","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>Automate restore from Rubrik with PowerShell - dbi Blog<\/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.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automate restore from Rubrik with PowerShell\" \/>\n<meta property=\"og:description\" content=\"One of our customer uses Rubrik as a solution to recover, manage, and secure all their data. In this context, Rubrik is used to backup and restore all the SQL Server instances. We need to run restore tests in this validated environment every year. To do so we decide to create a PowerShell script to [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-25T06:43:06+00:00\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Savorgnano\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Savorgnano\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 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\/automate-restore-from-rubrik-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\"},\"author\":{\"name\":\"St\u00e9phane Savorgnano\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"headline\":\"Automate restore from Rubrik with PowerShell\",\"datePublished\":\"2021-05-25T06:43:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\"},\"wordCount\":662,\"commentCount\":1,\"keywords\":[\"Backup\",\"PowerShell\",\"Restore\",\"Rubrik\",\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\",\"name\":\"Automate restore from Rubrik with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-05-25T06:43:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automate restore from Rubrik 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\/b6bce7d75118b35bdb3b439ad6a9ca3c\",\"name\":\"St\u00e9phane Savorgnano\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Savorgnano\"},\"description\":\"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals &amp; Pharmaceuticals, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Automate restore from Rubrik with PowerShell - dbi Blog","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\/automate-restore-from-rubrik-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Automate restore from Rubrik with PowerShell","og_description":"One of our customer uses Rubrik as a solution to recover, manage, and secure all their data. In this context, Rubrik is used to backup and restore all the SQL Server instances. We need to run restore tests in this validated environment every year. To do so we decide to create a PowerShell script to [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2021-05-25T06:43:06+00:00","author":"St\u00e9phane Savorgnano","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Savorgnano","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/"},"author":{"name":"St\u00e9phane Savorgnano","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"headline":"Automate restore from Rubrik with PowerShell","datePublished":"2021-05-25T06:43:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/"},"wordCount":662,"commentCount":1,"keywords":["Backup","PowerShell","Restore","Rubrik","SQL Server"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/","name":"Automate restore from Rubrik with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-05-25T06:43:06+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/b6bce7d75118b35bdb3b439ad6a9ca3c"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/automate-restore-from-rubrik-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Automate restore from Rubrik 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\/b6bce7d75118b35bdb3b439ad6a9ca3c","name":"St\u00e9phane Savorgnano","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/88d2a790f775c52c1012ec644d883431da758f2cbcfc16067ade04d2ef625ef5?s=96&d=mm&r=g","caption":"St\u00e9phane Savorgnano"},"description":"St\u00e9phane Savorgnano has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. St\u00e9phane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. St\u00e9phane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking \/ Financial Services, Chemicals &amp; Pharmaceuticals, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-savorgnano\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16370","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\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16370"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16370\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16370"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}