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

Why would you use this vs the native SQL Server backup/restore tools? — OMG Ponies
– OMG Ponies, Commented Jan 18, 2011 at 22:05
I'm not familiar to Sql server backup/restore tool, what I'm trying to accomplish here is to backup the particular database on daily basis to another remote server. would sql server backup/restore tool satisfy the need? — walter
– walter, Commented Jan 18, 2011 at 22:14
Yes, that's exactly what the backup/restore features are there for. If you think about it you are using the same Integration Service "objects" as they do. You are essentially writing a new front end for it. — Tony
– Tony, Commented Jan 18, 2011 at 22:48

Ariel · Accepted Answer · 2011-01-19 01:50:53Z

5

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.

answered Jan 19, 2011 at 1:50

Ariel

5,8606 gold badges52 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

walter Over a year ago

Yes, thank for the help.Ill look into this. but still I'm puzzled by the issue because when I run the code locally, it works, but when I upload it to the server where it's supposed to reside in, it gives that error, given that the ssis is installed and full permissions are granted to the logins, ⊙ˍ⊙?

marc_s · Accepted Answer · 2011-01-19 05:38:50Z

3

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;

edited Jan 19, 2011 at 5:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 19, 2011 at 1:41

Divi

7,72913 gold badges51 silver badges66 bronze badges

3 Comments

walter Over a year ago

thanks divi, you've made a good point, if automating it is possible then we have a great solution

Divi Over a year ago

I haven't really tried this, but I do run these commands manually as we don't have direct access to run backup commands on the server database. Happy to help

marc_s Over a year ago

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

Chris B. Behrens · Accepted Answer · 2011-01-18 22:04:50Z

0

Does the box you're running the application on have IS installed?

answered Jan 18, 2011 at 22:04

Chris B. Behrens

6,2938 gold badges49 silver badges74 bronze badges

3 Comments

Chris B. Behrens Over a year ago

What is the security context in which your application runs? Try adding it to the administrator's group, and see if that fixes it. Also, OMG Ponies above is right...you ought to take a look at the backup restore / replication stuff.

walter Over a year ago

database replication...hm..sounds like a db admin's job, as the nature of being a programmer I'm instinctively leaning to do this job programmatically..

Chris B. Behrens Over a year ago

@walter - definitely. I've spent much of my career being a poor man's dba.

Spencer · Accepted Answer · 2014-06-18 14:42:39Z

0

You can use the Sql Server Management Objects:

http://technet.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.aspx

Using the scripter class you can script the entire database, then run that script.

answered Jun 18, 2014 at 14:42

Spencer

2712 silver badges8 bronze badges

Collectives™ on Stack Overflow

Copy database from one server to another using c#

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related