Drop SQL database using powershell

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Robin16 Robin16 4 years ago.

  • Author
    Posts
  • #7901
    Profile photo of Robin16
    Robin16
    Participant

    Hi,

    I'm trying to check if the database exists, if exists drop the database.

    Please see the code below and let me know for any changes...code is working but database is not being dropped.

     

    Thanks in advance...

    
    Param
    
    (
    
    $DestServer,
    
    $targetdatabase
    
    )
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    
    $DataSet = New-Object System.Data.DataSet
    
     
    
    $SqlConnection.ConnectionString = "Server = $DestServer; Integrated Security = True"
    
     
    
    $SqlCmd.CommandText = "select name From sys.databases where name = "targetdatabase'"
    
     
    
    $SqlCmd.Connection = $SqlConnection
    
    $SqlAdapter.SelectCommand = $SqlCmd
    
    $SqlAdapter.Fill($DataSet)|out-null
    
     
    
    $rowCount_ = $Dataset.Tables[0].Rows.Count
    
     
    
    if($rowCount_ -eq 1)
    
    {
    
    echo "Database exists $targetdatabase"
    
    #Going to proceed with dropping database.
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    
    $DataSet = New-Object System.Data.DataSet
    
    $SqlConnection.ConnectionString = "Server = $DestServer; Database = '$targetdatabase'; Integrated Security = True"
    
     
    
    $SqlCmd.CommandText = "ALTER Database "$targetdatabase" SET Restricted_User WITH ROLLBACK IMMEDIATE
    
    DROP Database "$targetdatabase""
    
     
    
    #$SqlCmd.CommandText = "Drop Database $targetdatabase"
    
     
    
     
    
    #$SqlCmd.Connection = $SqlConnection
    
    #$SqlAdapter.SelectCommand = $SqlCmd
    
    #$SqlAdapter.Fill($DataSet)|out-null
    
    $SqlCmd.ExecuteNonQuery()
    
    echo "Successfully dropped database $targetdatabase"
    
    }
    
    else
    
    {
    
    echo "Database Does not exits"
    
    }
    
    
  • #7902
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    I'd use SMO

    import-module sqlps
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server("w12scorc")
    $server.Databases
    $server.databases["mydb"]
    $server.databases["mydb"].Drop()
    $server.databases

  • #7903
    Profile photo of Robin16
    Robin16
    Participant

    Thanks for the response.....

    yes we can use SMO...but this is just a part of my project....i have to get the database file names, i need to modify the file names....to much coding needs to be done using T SQL...so using the above methodology....

    btw can i use SMO in above code ?

You must be logged in to reply to this topic.