Drop SQL database using powershell

Welcome Forums General PowerShell Q&A Drop SQL database using powershell

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
5 years, 4 months ago.

  • Author
    Posts
  • #7901

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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 ?

The topic ‘Drop SQL database using powershell’ is closed to new replies.