Share via

Extract Data-tier application ?

techresearch7777777 1,801 Reputation points
2021-03-16T20:10:30.893+00:00

Hello, a user has requested for me to run within SQL Server Management Studio -> Tasks -> Extract Data-tier Application

I'm not familiar with this and type of request if it's safe for me as DBA to run and provide to user.

I read Microsoft's Doc about this and still not quite sure I understand (haven't wore my Development hat for many years and not up to date on that end).

Is it correct to say that it will export all of a DB schema and all of it's data?

And is running this on Production risky like will it lock the DB, cause slow performance, or any other affects/concerns?

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,950 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. techresearch7777777 1,801 Reputation points
    2021-03-16T20:18:30.877+00:00

    Sorry I mistyped wrong option.

    My question is in reference to the other one listed named 'Export Data-tier Application'.

    Thanks.

    0 comments
  2. Erland Sommarskog 102.6K Reputation points
    2021-03-16T21:18:43.957+00:00

    I think a Data-Tier application is only DDL, not data. No wait, I think one is bacpac - which definitely is data.

    In any case, I see little reason to run this on production. Restore a backup on an idle server for the task, unless you already have such an environment. (And exporting all data from the production database is certainly asking for trouble.)

    0 comments
  3. CathyJi-MSFT 21,101 Reputation points Microsoft Vendor
    2021-03-17T06:50:34.157+00:00

    Hi @techresearch7777777 ,

    > Is it correct to say that it will export all of a DB schema and all of it's data?

    Export Data-tier Application----Exporting a database creates a BACPAC file. This contains both schema and data.

    Extract Data-tier application ----Extracting a database creates a DACPAC file which only has schema.

    A DACPAC is generally used for deploying schema to a test, development or production environment. A BACPAC is generally used for migrating a database in and out of Azure SQL Database or to other servers.

    Refer to MS document Data-tier Applications.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments
  4. techresearch7777777 1,801 Reputation points
    2021-03-17T10:40:56.82+00:00

    Thanks for the replies Erland and Cathyji, much appreciated.

    Is it correct to say [Extract Data-tier Application] includes Logins/Users like security/sensitive data...wondering if this is something for me as DBA should not provide to user ? (Sounds like a very unusual request).

    And will it cause performance/negative affects while running on Prod?

    Our DB is very large and we don't have extra free space to restore to a test location to run this.

    0 comments
  5. Nandan Hegde 29,911 Reputation points MVP
    2021-03-17T11:11:19.983+00:00

    Hey @techresearch7777777 ,
    You can follow the below link :
    https://sqlplayer.net/2018/10/how-to-create-dacpac-file/
    of Creating DACPAC using Visual Studio.

    In that scenario, you would be able to control what all details you want to pass into the DACPAC wherein you can even exclude or delete the users/logins etc.

    Extracting the DACPAC should take within 1-2 mins via visual studio and ideally it wont impact any performance.

    0 comments