locked
Daily Database Restore to Copy Database - Parameters RRS feed

  • Question

  • Hi there,

    I'd like to know exactly what parameters to place and where to place them. I think this runbook has exactly what we need to copy the DB to a new DB overnight.

    The Runbook:

    <#
    .SYNOPSIS
    The purpose of this runbook is to demonstrate how to restore a database to a new database using an Azure Automation workflow.
    .DESCRIPTION
    WARNING: This runbook deletes a database. The database which you will be restoring to will be deleted upon the next run of this runbook.
    This runbook is designed to restore a single database to a test database. It will first try to delete the old test database. Then it will create a new one with data from 24 hours ago.
    .PARAMETER SourceServerName
    This is the name of the server where the source database is located
    .PARAMETER SourceDatabaseName
    This is the name of the database being restored from
    .PARAMETER ActiveDirectoryUser
    This is the name of the Active Directory User used to authenticate with.
    Example: MyActiveDirectoryUser@LiveIDEmail.onmicrosoft.com
    .PARAMETER SubscriptionName
    This is the name of the subscription where the database is on.
    .PARAMETER HoursBack
    This is how many hours back you want the copy of the database to be restored too.
    .NOTES
    AUTHOR: Eli Fisher
    LASTEDIT: March 11, 2015
    #>
    workflow Set-DailyDatabaseRestore
    {
    param([Parameter(Mandatory=$True)]
    [ValidateNotNullOrEmpty()]
    [String]$SourceServerName,
    [Parameter(Mandatory=$True)]
    [ValidateNotNullOrEmpty()]
    [String]$SourceDatabaseName,
    [Parameter(Mandatory=$True)]
    [ValidateNotNullOrEmpty()]
    [String]$ActiveDirectoryUser,
    [Parameter(Mandatory=$True)]
    [ValidateNotNullOrEmpty()]
    [String]$SubscriptionName,
    [Parameter(Mandatory=$True)]
    [ValidateNotNullOrEmpty()]
    [int]$HoursBack
    )
    #Configure PowerShell credentials and connection context
    $Cred = Get-AutomationPSCredential -Name $ActiveDirectoryUser #Replace this with the account used for Azure Automation authentication with Azure Active Directory
    Add-AzureAccount -Credential $Cred
    Select-AzureSubscription -SubscriptionName $SubscriptionName #Replace this with your subscription name
    #Set the point in time to restore too and the target database
    $PointInTime = (Get-Date).AddHours(-$HoursBack) #This gets the point in time for the database restore
    $TargetDatabaseName = $SourceDatabaseName + "_Test" #Replace this with the name of the database you want to restore to
    Write-Output "Deleting the old $TargetDatabaseName"
    #Delete the old database copy
    Remove-AzureSqlDatabase -ServerName $SourceServerName -DatabaseName $TargetDatabaseName -Force #Delete the day old copy database.
    Write-Output "Creating new $TargetDatabaseName with data at time $PointInTime"
    #Start the database restore to refresh the data
    Start-AzureSqlDatabaseRestore -SourceServerName $SourceServerName -SourceDatabaseName $SourceDatabaseName -TargetDatabaseName $TargetDatabaseName -PointInTime $PointInTime

    }

    When prompted, I fill in the parameters in the Azure Portal.

    I have input our server:

    xxxxxxx.xxxxxxxx.windows.net

    Database name:

    XXDB

    Active Directory User:

    erin@XXXXXXX.com

    Subscription Name:

    Pay-As-You-Go

    and Hours Back:

    0

    The errors I receive show me I am missing something pretty big. Please let me know if I am using the correct information and if I am placing it correctly.

    Thanks very much,

    Erin

    • Moved by Bill_Stewart Tuesday, December 11, 2018 9:39 PM This is not "fix/debug/rewrite this script I found on the Internet" forum
    Thursday, July 26, 2018 5:57 PM

All replies

  • Ask the author of the script  for help.  We cannot fix scripts found on the Internet.


    \_(ツ)_/

    Thursday, July 26, 2018 6:07 PM
  • Thank you. He has posted this script on the forum - Eli Fisher. I am not sure how to contact him within this forum.

    We are receiving the following errors:

    Add-AzureAccount : Cannot bind argument to parameter 'Credential' because it is null.
    At Set-DailyDatabaseRestore1:53 char:53

        + CategoryInfo          : InvalidData: (:) [Add-AzureAccount], ParameterBindingValidationException
        + FullyQualifiedErrorId : 
    ParameterArgumentValidationErrorNullNotAllowed,Microsoft.WindowsAzure.Commands.Profile.AddAzureAccount
     
    Select-AzureSubscription : The subscription name Pay-As-You-Go doesn't exist.
    Parameter name: name
    At Set-DailyDatabaseRestore1:54 char:54

        + CategoryInfo          : CloseError: (:) [Select-AzureSubscription], ArgumentException
        + FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.Profile.SelectAzureSubscriptionCommand
     
    Deleting the old Copy_NRDBH
    0a01fbfd-9089-48c4-9a0a-9244adfb070d:[localhost]:Client Session Id: '8632e6f2-bfb6-4520-bf7d-44e682b35f7e-2018-07-30 
    22:47:04Z'
    0a01fbfd-9089-48c4-9a0a-9244adfb070d:[localhost]:Client Request Id: ''
    Remove-AzureSqlDatabase : No default subscription has been designated. Use Select-AzureSubscription -Default 
    <subscriptionName> to set the default subscription.
    At Set-DailyDatabaseRestore1:63 char:63

        + CategoryInfo          : NotSpecified: (:) [Remove-AzureSqlDatabase], ArgumentException
        + FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.RemoveAzureSqlDatabase
     
    Creating new Copy_NRDBH with data at time 07/30/2018 21:47:04
    Start-AzureSqlDatabaseRestore : No default subscription has been designated. Use Select-AzureSubscription -Default 
    <subscriptionName> to set the default subscription.
    At Set-DailyDatabaseRestore1:67 char:67

        + CategoryInfo          : CloseError: (:) [Start-AzureSqlDatabaseRestore], ArgumentException
        + FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.StartAzureSqlDatabaseRestore

    Any thoughts or possible fixes are appreciated.

    Best,

    Erin

    Monday, July 30, 2018 10:49 PM
  • We do not fix code found on the Internet.  This forum is for questions about scripting and not for end users wishing to have others fix their script.

    You also have not posted the script correctly.  It is unreadable and unmanageable. 


    \_(ツ)_/

    Monday, July 30, 2018 10:57 PM
  • Here is the Q&A page for the script.  This is where you contact the author.

    https://gallery.technet.microsoft.com/scriptcenter/Azure-SQL-Database-Daily-cbd4f15d/view/Discussions#content


    \_(ツ)_/

    Monday, July 30, 2018 11:00 PM