Drop SQL database using powershell

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

Viewing 2 reply threads
  • Author
    Posts
    • #7901
      Participant
      Topics: 15
      Replies: 34
      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
      Topics: 0
      Replies: 669
      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
      Topics: 15
      Replies: 34
      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 ?

Viewing 2 reply threads
  • The topic ‘Drop SQL database using powershell’ is closed to new replies.