notebooks/dotNETNotebooks/dbatools/02-BackupsRestores.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": ".net-powershell",
            "display_name": ".NET (PowerShell)",
            "language": "PowerShell"
        },
        "language_info": {
            "name": "PowerShell",
            "version": "7.0",
            "mimetype": "text/x-powershell",
            "file_extension": ".ps1",
            "pygments_lexer": "powershell"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Backups\n",
                "\n",
                "***You will need to have followed the steps in the 00-CreateContainers notebook to use this notebook***\n",
                "\n",
                "We have written a book which will give you a brilliant introduction to dbatools. It's called dbatools in a Month of Lunches and you can find it at https://beard.media/book\n",
                "\n",
                "dbatools is **awesome** at performing backups :-)\n",
                "\n",
                "The next block sets the variables for tje instances and folder paths for this Notebook and checks the connection - Refer to the first notebook for any issues"
            ],
            "metadata": {
                "azdata_cell_guid": "8a02ce5a-bda9-4abd-813c-c8e261631e8c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbatoolsdemo'\n",
                "$SqlInstances = 'localhost,15592', 'localhost,15593'\n",
                "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred\n",
                "Write-Output \" Creating connection to the containers\"\n",
                "try {\n",
                "    $SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
                "    $SQL2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\n",
                "    Write-Output \"We have a connection to the containers\"\n",
                "\n",
                "}\n",
                "catch {\n",
                "    Write-Output \"You haven't got a connection to the containers - Either they are still upgrading in which case try again in 30 seconds or the containers have not come up correctly\"\n",
                "    Write-Output \"Make sure the containers are running - the code is below in a block for you\"\n",
                "    Write-Output \"docker ps -a\"\n",
                "    Write-Output \"If they are read the logs - the code is below in a block for you\"\n",
                "    Write-Output \"docker logs dbatools_SQL2019_1\"\n",
                "    Write-Output \"docker logs dbatools_SQL2019-1_1\"\n",
                "}\n",
                "# Run this first to make sure output width does not mess with output - Update output buffer size to prevent clipping in Visual Studio output window.\n",
                "if( $Host -and $Host.UI -and $Host.UI.RawUI ) {\n",
                "    $rawUI = $Host.UI.RawUI\n",
                "    $oldSize = $rawUI.BufferSize\n",
                "    $typeName = $oldSize.GetType( ).FullName\n",
                "    $newSize = New-Object $typeName (500, $oldSize.Height)\n",
                "    $rawUI.BufferSize = $newSize\n",
                "  }"
            ],
            "metadata": {
                "azdata_cell_guid": "0fdc5436-f5cc-4d79-b516-4dc5df94046a",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Investigate a directory using the SQL Service Account\n",
                "\n",
                "We can investigate the file system from the viewpoint of the SQL Service Account using `Get-DbaFile` We are going to use that to show the files in the directory but this can be really useful when investigating errors with file system access, for example when backups fail or when reading from shares. This command will try ot access the path from teh SQL Instance using the SQL Account which means that you can troubleshoot the error and also that users do not need access to directories holding backups or client files for example  \n",
                "If we look in the Directory that we created in the create container notebook using the command below"
            ],
            "metadata": {
                "azdata_cell_guid": "78ada984-f167-40c5-8f09-caa7e1b4121b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaFile -SqlInstance $SQL1  -Path /var/opt/mssql/backups/SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "3a60e7a5-2780-4cc0-aa75-e4e3a56d7912"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can see that all there is in the directory is a dummy file that we created earlier when we set up the directories  \n",
                "\n",
                "## Check the last time that the databases were backed up\n",
                "\n",
                "Knowing the last time a database was backed up is often useful information and with dbatools it can be retrieved easily. (NB - I use Warning action silently continue to hide the backups from multiple forks warnings that I have created when I created the container and havent fixed yet!)"
            ],
            "metadata": {
                "azdata_cell_guid": "c45e021c-a7e8-44d6-ba4b-660dbdf3034f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaLastBackup -SqlInstance $SQL1,$SQL2  -WarningAction SilentlyContinue | Format-Table "
            ],
            "metadata": {
                "azdata_cell_guid": "09ad58f2-ed50-49ed-ab1b-18fcdb958c88"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "The databases were last backed up on the 21st December 2019. Thats not so clever!\n",
                "\n",
                "## Docker requires permissions\n",
                "\n",
                "You will get errors for the command below if the account that you have used to allow Docker to access your drives does not have permissions to the folder that we have created. [You can see the instructions here](https://docs.docker.com/docker-for-windows/)\n",
                "\n",
                "You should add the docker account with full control over your dbatoolsdemo directory in your User Profile directory if you get errors below.\n",
                "\n",
                "![DockerCompose](.\\images\\dbatoolsdemopermissions.png )\n",
                "\n",
                "Its annoying but you can give the docker account modify permissions to your documents folder and it will inherited\n",
                "\n",
                "![DockerCompose](.\\images\\documentsdirectorypermissions.png )\n",
                "\n"
            ],
            "metadata": {
                "azdata_cell_guid": "85a2f0ca-b938-4b90-b0d6-67715aa74a79"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Perform a backup of the entire instance\n",
                "\n",
                "With one line of code we can quickly backup an entire instance."
            ],
            "metadata": {
                "azdata_cell_guid": "00469695-b66d-41d5-9c47-8671c40455e3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Backup-DbaDatabase  -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "b7ab61aa-1fc6-429e-a3ea-1dd7392227e6"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "All of the databases are backed up - Lets have a look in the directory"
            ],
            "metadata": {
                "azdata_cell_guid": "ad05055f-fe68-49ba-ab15-f8dadec4fb74"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaFile -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "efeea9db-76b8-4dcb-83d2-937243622a4a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you want to have a look in the mounted volume on your laptop "
            ],
            "metadata": {
                "azdata_cell_guid": "1d631fd2-2a59-48eb-8bbe-ee9ed61e468e"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-ChildItem \"$FolderPath\\SQL1\""
            ],
            "metadata": {
                "azdata_cell_guid": "6d40b6b7-11dc-46f5-926a-25d47c0e139e"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Backup to a directory for each database\n",
                "\n",
                "Rob - I hear you cry, I have 2000 databases, I dont want them all backing up to the same directory - Can dbatools help me there ?\n",
                "\n",
                "Sure, just add the `-CreateFolder` switch.\n",
                "\n",
                "Lets demonstrate with SQL2\n",
                "\n",
                "Theres nothing up my sleeves, lets look at the SQL2 folder\n"
            ],
            "metadata": {
                "azdata_cell_guid": "250afd5b-7e95-40a9-a658-622df0b66988"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaFile -SqlInstance $SQL2 -Path /var/opt/mssql/backups/SQL2"
            ],
            "metadata": {
                "azdata_cell_guid": "1bb7509c-91eb-4a59-aec2-81c56d203a7a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Only the dummy file! \n",
                "\n",
                "### Backup the databases"
            ],
            "metadata": {
                "azdata_cell_guid": "bfd29eef-0a46-4900-a9d1-7bce57a53d2c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Backup-DbaDatabase -SqlInstance $SQL2  -CreateFolder -Path /var/opt/mssql/backups/SQL2 -CompressBackup -WarningAction SilentlyContinue"
            ],
            "metadata": {
                "azdata_cell_guid": "5bed2ffe-a7cd-49f5-af20-079c4cf7c6a4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## have a look in the directory"
            ],
            "metadata": {
                "azdata_cell_guid": "c31e88df-1695-433b-9085-a948663aa45d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-ChildItem \"$FolderPath\\SQL2\" -Recurse"
            ],
            "metadata": {
                "azdata_cell_guid": "0af423bd-029f-45bd-bb27-95d60107ee59"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "As you can see we have the databases backed up, each in their own directory :-)"
            ],
            "metadata": {
                "azdata_cell_guid": "abfdea7b-8c74-4ab0-895d-2ac065c36881"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Double check all the instances\n",
                "\n",
                "dbatoos works with multiple instances whereever there is a `-SqlInstance` parameter\n",
                "\n",
                "Lets check the last time that these databases were backed up on both of the containers"
            ],
            "metadata": {
                "azdata_cell_guid": "94de8bdd-44e6-4e2a-aafd-78f373011ed5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaLastBackup -SqlInstance $SQL1,$SQL2  -WarningAction SilentlyContinue | Format-Table "
            ],
            "metadata": {
                "azdata_cell_guid": "d453929d-b352-48f4-a4c6-837d8c2f0c33"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# We want to use Ola Hallengren for our backups\n",
                "\n",
                "Thats ok, dbatools supports many of the excellent community tooling including Ola Hallengrens solution.\n",
                "\n",
                "You can install Ola Hallengren like this (except in containers)"
            ],
            "metadata": {
                "azdata_cell_guid": "c850b8bd-2e8f-409a-a4e5-fdcae21ccc83"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "$installDbaMaintenanceSolutionSplat = @{\r\n",
                "    SqlInstance = $sql1,$sql2\r\n",
                "    InstallJobs = $true\r\n",
                "    ReplaceExisting = $true\r\n",
                "    LogToTable = $true\r\n",
                "    OutputFileDirectory = '/var/opt/mssql/backups/SQL1'\r\n",
                "    BackupLocation = '/var/opt/mssql/backups/SQL1'\r\n",
                "    CleanupTime = 72\r\n",
                "    Solution = 'All'\r\n",
                "    Database = 'master'\r\n",
                "}\r\n",
                "Install-DbaMaintenanceSolution @installDbaMaintenanceSolutionSplat"
            ],
            "metadata": {
                "azdata_cell_guid": "82e9376c-9403-4c34-8414-4e103e1c4799"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Lets have a look at the Agent Jobs on the Instance"
            ],
            "metadata": {
                "azdata_cell_guid": "6ec8b7ae-9572-4c58-b456-4274a7ef8cf5"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgentJob -SqlInstance $sql1 |Select Name"
            ],
            "metadata": {
                "azdata_cell_guid": "d726d2e0-088b-4ad4-a561-595e3e92020d"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Those look like Ola Jobs to me :-)\n",
                "\n",
                "Lets run the jobs to back up the system and user databases"
            ],
            "metadata": {
                "azdata_cell_guid": "a78999ff-2897-4b40-a9b9-444e87a6446d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$Jobs = 'DatabaseBackup - SYSTEM_DATABASES - FULL' ,'DatabaseBackup - USER_DATABASES - FULL'\n",
                "#Start-DbaAgentJob -SqlInstance $sql1 -Job $Jobs"
            ],
            "metadata": {
                "azdata_cell_guid": "64782957-123c-40be-bca4-fa4e1a0ffc8b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Check the status of the jobs"
            ],
            "metadata": {
                "azdata_cell_guid": "792d582c-9d2f-43a5-a784-e73fb34f0562"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
                "Get-DbaAgentJob -SqlInstance $sql1 -Job $Jobs | Select SqlInstance, Name, CurrentRunStatus, LastRunOutCome"
            ],
            "metadata": {
                "azdata_cell_guid": "f1844102-2985-4481-90a4-69201250b7e8"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "When the jobs have finished, lets have a look at the files."
            ],
            "metadata": {
                "azdata_cell_guid": "f3aadd24-cca4-4b35-b8dd-d3ac5a368e5f"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaFile -SqlInstance $sql1 -Path '/var/opt/mssql/backups/SQL1/f443490967e7/AdventureWorks2017/FULL'"
            ],
            "metadata": {
                "azdata_cell_guid": "921b24d2-7e38-43ec-8ad1-ea467f78b165"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Restores\n",
                "\n",
                "Backups are all very well and good but what about restores?\n",
                "\n",
                "**NEVER EVER DO THIS IN PRODUCTION**\n",
                "unless you need to delete all of your user databases for some reason"
            ],
            "metadata": {
                "azdata_cell_guid": "8b9735d7-6a4b-4ce6-8bbf-7eb6e634fa21"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeAllSystemDb | Remove-DbaDatabase -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "9ef35f18-2a3d-4443-ab8e-a27e8abd8e60"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeAllSystemDb | Select Name"
            ],
            "metadata": {
                "azdata_cell_guid": "9acff129-d052-4d26-890e-70080a5a6177"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "All the databases have gone - How easy it is to restore them?\n",
                "\n",
                "This easy :-)\n",
                "\n",
                "One line of code\n",
                "\n",
                "## First lets restore from the backups we took with dbatools in, with all files in the one directory"
            ],
            "metadata": {
                "azdata_cell_guid": "32fc86d4-6784-452c-a1b2-b9c53a2c0715"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Write-Output \"Starting Restoring\"\n",
                "Restore-DbaDatabase  -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1 \n",
                "Write-Output \"Finished\""
            ],
            "metadata": {
                "azdata_cell_guid": "616c3d39-d5cb-43f4-be1d-d9a9643e451f"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeAllSystemDb | Select Name"
            ],
            "metadata": {
                "azdata_cell_guid": "dc9c2166-ddf6-4e2f-9856-830c8239343a"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "That was easy - WHat about if I have used Ola Hallengren?\n",
                "\n",
                "AGAIN\n",
                "\n",
                "**NEVER EVER DO THIS IN PRODUCTION**\n",
                "unless you need to delete all of your user databases for some reason\n"
            ],
            "metadata": {
                "azdata_cell_guid": "50abecaf-6012-4b33-8cb5-557fcf8cde22"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeAllSystemDb | Remove-DbaDatabase -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "917ae996-6484-4b05-83a6-6541c62d7920"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Restore from Ola Halengren Backups \n",
                "\n",
                "- Use the path from the above files check with the new container name "
            ],
            "metadata": {
                "azdata_cell_guid": "878c2db2-0d3d-4be8-be77-5436c6304cf5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Write-Output \"Starting Restoring\"\n",
                "Restore-DbaDatabase -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1/f443490967e7 -MaintenanceSolutionBackup\n",
                "Write-Output \"Finished\""
            ],
            "metadata": {
                "azdata_cell_guid": "fbf05b04-d32b-4fec-82df-5a458f1d613b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "$SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeAllSystemDb | Select Name"
            ],
            "metadata": {
                "azdata_cell_guid": "93944057-856c-4821-8362-21384bac41f3"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Thats all very well and good\n",
                "\n",
                "## When were the databases restored?\n",
                "\n",
                "and which backup was used?"
            ],
            "metadata": {
                "azdata_cell_guid": "c8bc1e26-6936-4d83-aa8d-70aacd7895f4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDbRestoreHistory -SqlInstance $SQL1 -Last"
            ],
            "metadata": {
                "azdata_cell_guid": "a3178577-17e3-4778-83ae-241512523806"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Don't forget to clean up\n",
                "\n",
                "Now you can use these containers to run the rest of the notebooks - Don't forget to run the Clean Up Containers Notebook at the end to clean up"
            ],
            "metadata": {
                "azdata_cell_guid": "52e22e31-3690-489e-8e11-281de30a162b"
            }
        }
    ]
}