locked
Scripting a monthly SQL headache RRS feed

  • Question

  • I don't know very much SQL. I know how to perform a procedure that is done monthly. I've tried learning more over the past few months, but I feel like that has only made me for dangerous.

    I would like to run a script that would do the following:

    --Connect to SQL Server (Optional as I can access the SQL Server directly)

    --Detach a DB from SQL

    --Create a blank database

    --Recover the newly created DB from a backup taken at 3am (The backup is done outside of this project)

    --edit a  ini file located on another server (Optional I can take care of this manually but it would be a great feature)

     ### Run the code on line 2 if dbatools are not installed
     ###Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

    $Server = "SQL_Server_0101"
    $Creds = "SQL_DB_Creds"

    ### Adds Presentation Core and Framework allowing message box
    Add-Type -AssemblyName PresentationCore,PresentationFramework

    ### Defines the messagebox params
    $ButtonType = [System.Windows.MessageBoxButton]::YesNo
    $MessageboxTitle = “Source and Target”
    $MessageboxbodySource = “You have chosen $SourceDatabase as your recovery source?"
    $MessageboxbodyTarget = “You have chosen $TargetDatabase as your recovery source?”
    $MessageIcon = [System.Windows.MessageBoxImage]::Warning

    ### This was original code snipit. will remove after testing [System.Windows.MessageBox]::Show($Messageboxbody,$MessageboxTitle,$ButtonType,$messageicon)

    ### Select the Source of recovery then confirms your selection
    $SourceDatabase = Show-SqlDatabaseList -SqlServer $Server -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show($MessageboxbodySource,$MessageboxTitle,$ButtonType,$messageicon)


    ### Select the Target of recovery then confirms your selection
    $TargetDatabase = Show-SqlDatabaseList -SqlServer $Server -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show($MessageboxbodyTarget,$MessageboxTitle,$ButtonType,$messageicon)

    ###We now have our source and target in the varabile $SourceDatabase and $TargetDatabase
    $DomainCreds = "$env:USERDOMAIN"+"\"+"$env:USERNAME"

    ### Create a new, blank Database
    $s = New-PSSession -ComputerName $Server -Credential $DomainCreds
    Invoke-Command -Session $s -ScriptBlock {Invoke-Sqlcmd -ServerInstance $Server -InputFile "U:\blank_DB.sql"}

    ### Restores Newly created database from a backup
    Restore-SqlBackupFromDirectory -SqlServer $TargetDatabase -Path "S:\SQLBACKups\CompanyName\3AM"

    Monday, July 3, 2017 9:19 PM

Answers

  • Start simple and learn to use PowerShell.  Most of your issues are PowerShell and not SQL:

    Here is a way to start then solve each issue as it arises.

    $instance = 'SQL_Server_0101'
    $sqluser = 'SQL_DB_Creds'
    Add-Type -AssemblyName System.Windows.Forms
    
    $SourceDatabase = Show-SqlDatabaseList -SqlServer $instance -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show("You have chosen $SourceDatabase as your recovery source?", "Source and Target", 'YesNo', 'Warning')
    
    $TargetDatabase = Show-SqlDatabaseList -SqlServer $Server -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show("You have chosen $TargetDatabase as your recovery source?", "Source and Target", 'YesNo', 'Warning')
    
    Invoke-Sqlcmd -ServerInstance $Server -InputFile 'U:\blank_DB.sql' -Username $sqluser -Password $pwd
    
    Restore-SqlBackupFromDirectory -SqlServer $TargetDatabase -Path "S:\SQLBACKups\CompanyName\3AM"
    
    
    


    \_(ツ)_/

    • Marked as answer by dus0922 Tuesday, July 4, 2017 1:36 AM
    Monday, July 3, 2017 10:06 PM

All replies

  • And what is your question?  You have given us a recipe and some code but you have not said what is not happening or what your srror is.

    I would recommend using a SQL Task for this.

    I also suggest reading the help for the commands you are trying to use.  YOU need to understand where the files are for your paths and the relationship of the task to the network and SQLServer.

    There is no need to use a PsSession for this.  There is no need to load the PresentationFramework for this.


    \_(ツ)_/

    Monday, July 3, 2017 9:56 PM
  • Start simple and learn to use PowerShell.  Most of your issues are PowerShell and not SQL:

    Here is a way to start then solve each issue as it arises.

    $instance = 'SQL_Server_0101'
    $sqluser = 'SQL_DB_Creds'
    Add-Type -AssemblyName System.Windows.Forms
    
    $SourceDatabase = Show-SqlDatabaseList -SqlServer $instance -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show("You have chosen $SourceDatabase as your recovery source?", "Source and Target", 'YesNo', 'Warning')
    
    $TargetDatabase = Show-SqlDatabaseList -SqlServer $Server -SqlCredential $Creds -Verbose
    [System.Windows.MessageBox]::Show("You have chosen $TargetDatabase as your recovery source?", "Source and Target", 'YesNo', 'Warning')
    
    Invoke-Sqlcmd -ServerInstance $Server -InputFile 'U:\blank_DB.sql' -Username $sqluser -Password $pwd
    
    Restore-SqlBackupFromDirectory -SqlServer $TargetDatabase -Path "S:\SQLBACKups\CompanyName\3AM"
    
    
    


    \_(ツ)_/

    • Marked as answer by dus0922 Tuesday, July 4, 2017 1:36 AM
    Monday, July 3, 2017 10:06 PM
  • Thank you!

    Maybe I should have been a little more clear in my original post...I'll keep that in mind next time.

    You have given me a great place to start. I didn't realize the message box was so simple.Thanks again

    Tuesday, July 4, 2017 1:35 AM