Is there any possibility to use SMO objects to copy a database from MS SQL Server 2014 Express (server address) to localhost?

Question

I need to implement in c# a functionality for copying a certain database from server address to localhost (I'm aware of the backup/restore option - I have this implemented when I want to export and import databases that are stored locally). I've tried the following source code but when I tried to run the transferData I get an error "The Integration Services component is not installed or you do not have permission to use it.". I'm using SQL Express 2014.I have installed also SharedManagementObjects and SQLSysClrTypes.msi. I've also noticed that in my c# solution I have some references to Smo assemblies like NuGet installation but they are for SQL Server 2008. Locally I can find some smo assemblies in C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies. Should I reference them in my project? I've tried the following code, but I'm getting exception in transferDatabase.TransferData();:

  Server sourceServer = new Server(sourceServerName);
  Server destinationServer = new Server(destinationServerName);
        try
        {
            sourceServer.ConnectionContext.LoginSecure = true;
            sourceServer.ConnectionContext.Connect();

            destinationServer.ConnectionContext.LoginSecure = true;
            destinationServer.ConnectionContext.Connect();
           
            Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName];
            Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName);

             databaseDestination.Create();

            Transfer transferDatabase = new Transfer(databaseSource)
            {
                CopyAllObjects = false,
                CopyAllSchemas = true,
                //Copy all user defined data types from source to destination
                CopyAllUserDefinedDataTypes = true,
                //Copy all tables from source to destination
                CopyAllTables = true
            };

     
            //Copy all constraints
            transferDatabase.Options.DriAllKeys = true;
            //Copy all defaults
            transferDatabase.Options.DriDefaults = true;
            //Drops the existing tables
            transferDatabase.DropDestinationObjectsFirst = true;
            //Copy data of all source tables to destination tables
            //It actually generates INSERT statement for destination
            transferDatabase.CopyData = true;
            //Copy all stored procedure from source to destination
            transferDatabase.CopyAllStoredProcedures = true;
            //specify the destination server name
            transferDatabase.DestinationServer = destinationServer.Name;
            //specify the destination database name
            transferDatabase.DestinationDatabase = databaseDestination.Name;
            //TransferData method transfers the schema objects and data
            //whatever you have specified to destination database
            transferDatabase.TemporaryPackageDirectory = @"C:\Data\";
            transferDatabase.Options.ContinueScriptingOnError = true;
            transferDatabase.TransferData();
        }
        catch (Exception ex)
        {
            log.Error(ex.Message);
        }
        finally
        {
            if (sourceServer.ConnectionContext.IsOpen)
            {
                sourceServer.ConnectionContext.Disconnect();
            }

            if (destinationServer.ConnectionContext.IsOpen)
            {
                destinationServer.ConnectionContext.Disconnect();
            }
        }

Answer 1

So it sounds like you're just looking for a database syncing solution but you don't want to have to go through the process of taking a backup and restoring it. Here's a few alternative options that may be less work than backups or coding your own solution:

1. SQL Examiner Suite - This tool let's you compare two databases, select individual objects, and sync the data and schema changes between them. I've had a lot of success with this tool in the past.

2. Generate a BACPAC Export - This is another way to export the data and schema from a database and import to a new one all through SSMS. (If you just need the schema, no data, then you can do a DACPAC export instead.)

3. Generate Scripts for the Database - With this solution, you can generate the scripts for the schema and data of the database using SSMS. You can then save those scripts to a single .sql file which you can open in SSMS on the local server, and run to create the entire database.