CODEWITHSUNDEEP
c#sql-serversmo
Wilhelm Kleu
Wilhelm Kleu

Reputation: 11047

How do I use SMO to transfer database to new schema?

I want to use Microsoft.SqlServer.Management.Smo to transfer database to a new database but with a new schema.

Here is what I am doing at the moment (I played around with the options to get the behaviour I want but without any luck):

  var source = new Server(GetServerConnection("sa"));
  var destination = new Server(GetServerConnection("schema_user1")); 

  source.ConnectionContext.Connect();
  destination.ConnectionContext.Connect();

  var sourceDatabase = source.Databases["support"];
  var destinationDatabase = destination.Databases["schema_test"];


  var transfer = new Transfer(sourceDatabase)
  {
    CopyAllObjects = false,
    CopyAllUserDefinedDataTypes = true,
    CopyAllTables = true,
    CopyData = true,
    CopyAllStoredProcedures = true,
    PreserveDbo = false,
    PreserveLogins = false,
    DestinationServer = destination.Name,
    DestinationDatabase = destinationDatabase.Name,
    DestinationLogin = destination.ConnectionContext.Login,
    DestinationPassword = destination.ConnectionContext.Password,
    DestinationLoginSecure = false,
    Options = {ScriptSchema = false}
  };

  transfer.TransferData();

I get the following error: 

The specified schema name "dbo" either does not exist or you do not have permission to use it.

This is because schema_user1 does not have permission to dbo (which I don't want it to have).

How can I transfer it to a new schema on the destination database?

Upvotes: 3

Views: 882

Answers (1)

Wilhelm Kleu
Wilhelm Kleu

Reputation: 11047

I had to specify Options.SchemaQualify = false for the Transfer object (which specifies if the schema must be used in the script or not).

Then using the user in the new database it will be created in the new schema.

Upvotes: 3

Related Questions