SMO.Transfer fails due to LocalAdmin login, but only for some databases

Question

I am attempting to copy an SQL Server database into a new database on the same server without transferring the data (that is, I'm trying to build an identical, empty database).

The code below works correctly on a sample database that I built specifically for testing. But when applied to the actual target database, it fails at the .TransferData() call (after several seconds of execution) with the error System.Data.SqlClient.SqlException : 'LocalAdmin' is not a valid login or you do not have permission. The account under which I'm developing and testing program can be used to log into the SQL Server instance in question and perform database creation, and creation of objects within both the source and target databases.

After the failure, the database specified in targetDb has, in fact, been created, but it has no tables or other structures.

Can anyone tell me how to fix the following code so that it will be able to successfully copy my database structure?

/* Ref: Microsoft.SqlServer.ConnectionInfo.dll
        Microsoft.SqlServer.Management.Sdk.Sfc.dll
        Microsoft.SqlServer.Smo.dll
        Microsoft.SqlServer.SmoExtended.dll
        Microsoft.SqlServer.SqlEnum.dll */

using Microsoft.SqlServer.Management.Smo;
using System;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            CopyEmptyDatabase(@"server_name\instance_name", "sourceDb_name", "targetDb_name");
        }

        public static void CopyEmptyDatabase(string instance, string sourceDb, string targetDb)
        {
            var server = new Server(instance);

            var transferAgent = new Transfer(server.Databases[sourceDb]);

            transferAgent.CopyAllObjects = true;
            transferAgent.DropDestinationObjectsFirst = true;
            transferAgent.CopySchema = true;
            transferAgent.CopyData = false;

            // Transfer requires the target DB to exist
            var target = new Database(server, targetDb);
            target.Create();

            transferAgent.DestinationServer = instance;
            transferAgent.DestinationDatabase = targetDb;
            transferAgent.DestinationLoginSecure = true;
            transferAgent.Options.IncludeIfNotExists = true;

            try
            {
                transferAgent.TransferData();
            }
            catch (Exception exc)
            {
                Debug.Print(exc.Message);
                throw;
            }

        }
    }
}

Answer 1

Try making a new db from the SQL tools and see if you can drop the sprocs that are in the newly created db. If you're not the dbo, you probably won't have permissions to drop them. Note that the objects therein are copied from the model db...so it won't be completely empty at the start. Your organization may have added things to the model as well...so you'll need to look into that.

Edit:

Another way to test this theory...just remove the line that says transferAgent.DropDestinationObjectsFirst = true;

Answer 2

• Login issues may be related to the service account that the query runs under. For example, replication uses the SQL Server Agent account by default to read into a fileshare. This also relates to what account is attempting to login to the target database.
• never write in plain text passwords.
• ensure the SUSER_ID is the same as logically, two identical but plain text login/users will be distinct from each other.

Try not to reinvent the wheel when it already is available to you. Unless this is an application (sounds risky), utilizing the tools you already possess will save time, create stable .sql files, and help control your changes to any PROD environments.

As a general rule, if I change anything on the schematics level for a PROD server, I save the script or prior schematics before implementing it. You never know when it can save your life.

1. Use SSMS and select GENERATE SCRIPTS In your Object Explorer, right click on the target database. Under the tab Tasks, select Generate Scripts...

   • By default, the settings are set to recreate all major (tables, schemas, Stored Procedures, etc) objects, but this does not include some intangible settings such as the data.

2. Set location Set the location of your scripts, such as a shared folder that everyone can find.

   • If you can avoid Unicode scripts, use the ANSI setting, as any large .sql files will require SQLCMD to execute.
   • Alternately, you can split the objects into their own files, to allow for version control.
3. Use the Advanced Settings You can set the version level of your scripts (as far back as SQL Server 2005) and the type of database engine.
   • You will be to specify Logins, Object-Level Permissions, Owners, and can even optionally include Statistics and their Histograms if your tests are the same as your PROD environment. IN general, let the Optimizer handle this.
   • Other objects, such as Triggers, may not be included, so be sure to go through the list for your environment.
4. Enjoy your new .sql scripts! That's all!

The best part is this used the native SSMS client to create and you have legitimate .sql files that any SQL Server edition will be able to read.