How to: Delete a Data-tier Application

Before You Begin

When you delete a data-tier application (DAC) instance, you choose one of three options specifying what is to be done with the database associated with the data-tier application. All three options delete the DAC definition metadata. The options differ in what they do with the database associated with the data-tier application. The wizard does not delete any of the instance-level objects associated with the DAC or database, such as logins.

There is no automatic mechanism to restore the DAC definition metadata or the database after you delete a DAC. How you can manually rebuild the DAC instance depends on the delete option.

Permissions

A DAC can only be deleted by members of the sysadmin or serveradmin fixed server roles, or by the database owner. The built-in SQL Server system administrator account named sa can also launch the wizard.

Delete a DAC Using PowerShell

Create a PowerShell script file (such as DeleteDAC.ps1) containing the following code.

1. Add code to create a SMO Server object and set it to the instance containing the DAC you want to delete. This example sets a Server object to the default instance on the local computer:

   ## Set a SMO Server object to the default instance on the local computer.
   CD SQLSERVER:\SQL\localhost\DEFAULT
   $srv = get-item .
   

2. Add code to open a ServerConnection object and connect to the same instance.

   ## Open a Common.ServerConnection to the same instance.
   $serverconnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($srv.ConnectionContext.SqlConnectionObject)
   $serverconnection.Connect()
   $dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverconnection)
   

3. Add code to subscribe to the DAC delete events.

   ## Subscribe to the DAC delete events.
   $dacstore.add_DacActionStarted({Write-Host `n`nStarting at $(get-date) :: $_.Description})
   $dacstore.add_DacActionFinished({Write-Host Completed at $(get-date) :: $_.Description})
   

4. Add code to specify the DAC to delete. This example specifies the DAC name:

   ## Specify the DAC to delete.
   $dacName  = "MyApplication"
   

5. Add code to run the Uninstall method with the information specified above. Use one of these three sets of code, depending on which delete option is appropriate:

   • To delete the DAC registration but leave the database intact:

     ## Only delete the DAC definition from msdb, the associated database remains active.
     $dacstore.Unmanage($dacName)
     

   • To delete the DAC registration and detach the database:

     ## Delete the DAC definition from msdb and detach the associated database.
     $dacstore.Uninstall($dacName, [Microsoft.SqlServer.Management.Dac.DacUninstallMode]::DetachDatabase)
     

   • To delete the DAC registration and drop the database:

     ## Delete the DAC definition from msdb and drop the associated database.
     ## $dacstore.Uninstall($dacName, [Microsoft.SqlServer.Management.Dac.DacUninstallMode]::DropDatabase)
     

Run DeleteDAC.ps1 from either a PowerShell session in which you have loaded the SQL Server PowerShell snapins, or by using the sqlps command prompt utility.

Using the Delete Data-tier Application Wizard

In Management Studio, you can launch the wizard by right-clicking a data-tier application node in the Object Explorer, and selecting Delete Data-tier Application….

Click on a link in the list below to navigate to the details for a page in the wizard:

• Introduction

• Choose Method

• Summary

• Delete Data-tier Application

Introduction Page

This page describes the steps for deleting a data-tier application.

Do not show this page again. - Click the check box to stop the page from being displayed in the future.

Next > - Proceeds to the Choose Method page.

Cancel - Ends the wizard without deleting a data-tier application or database.

Choose Method Page

Use this page to specify the method for deleting the data-tier application.

Delete registration - Removes the metadata defining the data-tier application, but leaves the associated database intact.

Detach database - Removes the metadata defining the data-tier application and detaches the associated database.

The database can no longer be referenced by that instance of the Database Engine, but the data and log files remain intact.

Delete database - Removes the metadata defining the DAC and drops the associated database.

The data and log files for the database are permanently deleted.

< Previous - Returns to the Introduction page.

Next > - Proceeds to the Summary page.

Cancel - Ends the wizard without deleting the DAC or database.

Summary Page

Use this page to review the actions the wizard will take when deleting the DAC instance.

Review your selection summary - Review the DAC, database, and deletion method displayed in the box. If the information is correct, select either Next or Finish to delete the DAC. If the DAC and database information is not correct, select Cancel and select the correct DAC. If the deletion method is not correct, select Previous to return to the Choose Method page and select a different method.

< Previous - Returns to the Choose Method page to choose a different delete method.

Next > - Deletes the DAC instance using the method you chose on the previous page, and proceeds to the Delete Data-tier Application page.

Cancel - Ends the wizard without deleting the DAC instance.

Delete Data-tier Application Page

This page reports the success or failure of the delete operation.

Deleting the DAC - Reports the success or failure of each action taken to delete the DAC instance. Review the information to determine the success or failure of each action. Any action that encountered an error will have a link in the Result column. Select the link to view a report of the error for that action.

Save Report - Select this button to save the deletion report to an HTML file. The file reports the status of each action, including all errors generated by any of the actions. The default folder is a SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account..

Finish - Ends the wizard.

See Also

Concepts

Other Resources

Change History