如何备份部分表记录到另一台机器

-- 建立链接服务器，并提供登录信息
-- 请修改 @datasrc 为备份服务名
exec sp_addlinkedserver @server=@#backupserver@#, @srvproduct=@#@#, @provider=@#SQLOLEDB@#, @datasrc=@#servercomputername@#
go
-- 请修改 @rmtuser、@rmtpassword 参数为备份服务器sql server登录帐号
exec sp_addlinkedsrvlogin @rmtsrvname=@#backupserver@#,@useself=@#false@#,@locallogin=null,@rmtuser=@#sa@#,@rmtpassword=@#pass@#
go


-- 把数据保存到备份服务器
-- dbname应修改备份服务器数据库名
insert into backupserver.dbname.dbo.masterTable
  select * from masterTable
    where keyField in (@#value1@#,@#value2@#)
go
insert into backupserver.dbname.dbo.masterTableDetail
  select * from masterTableDetail
    where (select keyField from masterTable where masterTableDetail.masterTableId=masterTable._id) in (@#value1@#,@#value2@#)
go

-- 删除本数据库记录
delete from masterTable
  where keyField in (@#value1@#,@#value2@#)
go
delete from masterTableDetail
  where (select keyField from masterTable where masterTableDetail.masterTableId=masterTable._id) in (@#value1@#,@#value2@#)
go

 

原文转自：http://www.ltesting.net