2

Goal

Clone a SQL database to a different remote SQL Server using a PowerShell script

What Works

Using SSMS to import the BACPAC file into different servers (remotely & local) works without (reported) warnings or errors.

What Doesn't

Importing the BACPAC into a remote SQL Server results in the following error using sqlpackage.exe & PowerShell dbatools:

Warning SQL72038: The object [XXX] already exists in database with a different definition and will not be altered.
Error SQL72014: .Net SqlClient Data Provider: Msg 15023, Level 16, State 1, Line 1 User, group, or role 'XXX' already exists in the current database.
Error SQL72045: Script execution error.  The executed script:
CREATE USER [XXX] FOR LOGIN [XXX];

I also tried using PS dbatools DACPAC approach: https://dbatools.io/clone/

The error message with different settings changed to:

Initializing deployment (Start)
                       The object [XXX] already exists in database with a different definition and will not be altered.
                       Initializing deployment (Complete)
                       Analyzing deployment plan (Start)
                       Analyzing deployment plan (Complete)
                       Reporting and scripting deployment plan (Start)
                       Reporting and scripting deployment plan (Complete)
                       Updating database (Start)
                       Creating NEW_DATABASE...
                       The database settings cannot be modified. You must be a SysAdmin to apply these settings.
                       Creating [XXX]...
                       .Net SqlClient Data Provider: Msg 15023, Level 16, State 1, Line 1 User, group, or role 'XXX' already
                       exists in the current database.
                       Script execution error.  The executed script:
                       CREATE USER [XXX] WITHOUT LOGIN;

An error occurred while the batch was being executed.
                       Updating database (Failed)

The next step was then to disable users and add back the required users & roles via a script. Using the following link as a reference, resulted in a database that I was unable to drop with our existing administrator login & password.

DacPac exclude users and logins on export or import

To fix this, we had to change our administrator password in RDS

AWS RDS SQL Server unable to drop database

Notes

  • I can't remove user XXX because it's mapped to different databases
  • SQL Server Management Studio v17.9.1
  • PowerShell dbatools v1.0.30

Questions

  • Is there a way to find out what SSMS is executing so that I can replicate it via a script?
  • What are the options to work around this issue?
andrewjamesbowen
  • 622
  • 8
  • 14
  • I'm not sure if you are using the right tool for the job. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver15 says that you cannot use import to an existing database unless it does not contain any user-defined objects. If you are using it to update a database schema, I think you need to use publish from dacpac. – Luke Briner May 19 '21 at 11:49
  • Thank you for the reply. The issue is, how would I clone a Production DB into a Testing SQL Server via CLI tools? i.e. I need every table, stored proc, row, etc. What I'm confused by is that SSMS works fine with bacpacs, yet I can't replicate that functionality with a CLI tool – andrewjamesbowen May 21 '21 at 22:45
  • Create an empty database and then use sqlpackage import. – Luke Briner May 23 '21 at 15:52

1 Answers1

5

This is very late to answer, although I will share my inputs here so someone in future can find it helpful to solve the problem.

This is what I did with some exceptions. Exclude the object types = Users;Permissions;RoleMembership;Logins; in the sql package command line property to successfully deploy the database.

The exception is, you will not be able to deploy your users, permissions on the already existing set. On a fresh install, you could remove this exclude property to deploy the entire set without errors.

Here is the commandline parameter to use in your sqlPackage.exe command

/P:ExcludeObjectTypes=Users;Permissions;RoleMembership;Logins;

Ref: MS DOCS -> https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver15

Jabez
  • 795
  • 9
  • 18