Copy database from one server to another using c#

Question

I'm trying to create a windows application that copies a database from one server to another using the Transfer object but end up having "The Integration Services component is not installed or you do not have permission to use it" error. both servers have enterprise edition of sql server 2005 and the integration services component installed, the connection logins have full permissions as well. I really don't know what goes wrong here

            Server backFromServer = new Server(@"xx.xx.xx.xx");
            Server backToServer = new Server(@"xx.xx.xx.xx");
            backFromServer.ConnectionContext.LoginSecure = false;
            backFromServer.ConnectionContext.Login = "username";
            backFromServer.ConnectionContext.Password = "password";



            backToServer.ConnectionContext.LoginSecure = false;
            backToServer.ConnectionContext.Login = "username";
            backToServer.ConnectionContext.Password = "password";


            Database backFromDb = new Database();
            backFromDb = backFromServer.Databases["databasesource"];

            Database backToDb = new Database();
            backToDb = backToServer.Databases["databasedest"];

            EventLog.WriteEntry(eventLogSource,"Loading databases successful!", EventLogEntryType.Information);

            Transfer dataTransfer = new Transfer(backFromDb);
            dataTransfer.CopyAllTables = true;
            dataTransfer.CopyAllObjects = false;
            dataTransfer.CopyData = true;
            dataTransfer.CopyAllUserDefinedDataTypes = true;
            dataTransfer.CopyAllStoredProcedures = false;

            dataTransfer.DropDestinationObjectsFirst = true;

            dataTransfer.Options.WithDependencies = false;

            dataTransfer.DestinationServer = backToServer.Name;
            dataTransfer.DestinationDatabase = backToDb.Name;
            dataTransfer.DestinationLoginSecure = false;
            dataTransfer.DestinationLogin = "username";
            dataTransfer.DestinationPassword = "password";


            EventLog.WriteEntry(eventLogSource,"Transfer configuration successful, starting to transfer!", EventLogEntryType.Information);

            dataTransfer.TransferData();//here causes the error

            EventLog.WriteEntry(eventLogSource, "Transfer successful!", EventLogEntryType.Information);

I managed to find a solution so the app does this: step 1. backup the database into a .bak file by using the Backup class

 Server backFromServer = new Server(@"server");
            backFromServer.ConnectionContext.LoginSecure = false;
            backFromServer.ConnectionContext.Login = "un";
            backFromServer.ConnectionContext.Password = "psd";
            Database backFromDb = new Database();
            backFromDb = backFromServer.Databases["dbname"];

            Backup bkpDatabase = new Backup();
            bkpDatabase.Action = BackupActionType.Database;
            bkpDatabase.Database = backFromDb.Name;
            bkpDatabase.Incremental = false;
            bkpDatabase.LogTruncation = BackupTruncateLogType.Truncate;
            bkpDatabase.Initialize = true;

            BackupDeviceItem bkpDevice = new BackupDeviceItem(@"c:\backup.bak", DeviceType.File);

            bkpDatabase.Devices.Add(bkpDevice);
            bkpDatabase.SqlBackup(backFromServer);

            EventLog.WriteEntry(eventLogSource, "Create database backup file successful!", EventLogEntryType.Information);

step 2. Since the file is in the source server, download the file. step 3. restore the database by using t-sql. step 4. fire up a scheduled task that runs the app on daily basis. here is the t-sql script: USE master ALTER DATABASE [DBName] SET Single_User WITH Rollback Immediate RESTORE DATABASE [DBName] FROM DISK = N'filepath' WITH REPLACE, FILE = 1, NOUNLOAD, STATS = 10 ALTER DATABASE [DBName] SET Multi_User

Answer 1

Create a sql job that does the, well, the job by doing backup & restore, log shipping, SQL Replication, etc. Doesn't sound like a problem you want to solve through an app running daily but a sql job instead, which will be more reliable, get monitoring, diagnostics for free, not to mention that you will need to run your app with high privileges, which is in itself a bad practice and a call for trouble.

Answer 2

Could you possibly create a sql command object with the following command?

BACKUP DATABASE DatabaseName
TO DISK = 'path\DatabaseName_Backup.bak'
WITH FORMAT, COPY_ONLY

and then restore using

RESTORE DATABASE SomeDatabase
  FROM DISK = 'path\DatabaseName_Backup.bak' 
  WITH FILE=1, 
    NORECOVERY;