使用c#将数据库从一台服务器复制到另一台

我正在尝试创建一个 Windows应用程序,使用Transfer对象将数据库从一个服务器复制到另一个服务器

但最终导致“未安装Integration Services组件或您没有使用它的权限”错误.

两台服务器都安装了sql server 2005的企业版,并且安装了集成服务组件,连接登录也具有完全权限.我真的不知道这里出了什么问题

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);

我设法找到了解决方案

所以应用程序执行此操作：

步骤1.使用Backup类将数据库备份到.bak文件中

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.启动每天运行应用程序的计划任务.

  这是t-sql脚本：

使用大师

ALTER DATABASE [DBName] SET Single_User WITH Rollback Immediate

RESTORE DATABASE [DBName] FROM DISK = N’filepath’WOR REPLACE,FILE = 1,NOUNLOAD,STATS = 10

ALTER DATABASE [DBName] SET Multi_User

创建一个sql作业,通过执行备份来完成工作.恢复,日志传送,SQL复制等.听起来不像你想要通过每天运行的应用程序来解决的问题,而是一个sql作业,这将更可靠,得到监控,免费诊断,更不用说你了将需要以高权限运行您的应用程序,这本身就是一个不好的做法和一个麻烦的呼吁.

查看更多关于使用c#将数据库从一台服务器复制到另一台的详细内容...