I’m trying to create a Windows application that copies a database from one server to another using the Transfer object but in the end, “The Integration Services component is not installed or you do not have permission to use it.” both servers have the corporate version of SQL Server 2005 and the integration services component is installed, connection accounts have full permissions. I really don't know what is 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();
EventLog.WriteEntry(eventLogSource, "Transfer successful!", EventLogEntryType.Information);
I managed to find a solution so the application does this: step 1. backing up the database to a .bak file 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);
2. , .
3. t-sql.
4. , . -sql script:
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