通过脚本把远程服务器上的表和数据拷贝到本地数据库

EXEC sp_addlinkedserver @server='srv_lnk', @srvproduct='', @provider='SQLOLEDB', @datasrc='HELIOS01.us.psav.dom\HELIOSLISTENER'
EXEC sp_addlinkedsrvlogin 'srv_lnk', 'false', NULL, 'ShinetechPSAV', 'CeJe5r+sp'

USE WorkForceManagement
BEGIN TRY
    --BEGIN TRANSACTION

    DROP TABLE Comments
    DROP TABLE Customer_Exclude
    DROP TABLE DeptStatus
    DROP TABLE Forecast_Hourly
    DROP TABLE Forecast_Revenue
    DROP TABLE Forecast_Salary
    DROP TABLE Forecast_Schedule_Change
    DROP TABLE Freelance
    DROP TABLE History
    DROP TABLE InvGroup
    DROP TABLE LastUpdateTables
    DROP TABLE [Login]
    DROP TABLE NWF_All_Employees
    DROP TABLE NWF_Closed_ACTUAL
    DROP TABLE NWF_Closed_FORECAST
    DROP TABLE NWF_Holidays
    DROP TABLE NWF_Live
    DROP TABLE NWF_QB_Perc
    DROP TABLE NWF_Sites
    DROP TABLE NWF_Supervisor_Name
    DROP TABLE NWF_Supervisor_Region
    DROP TABLE NWF_Supervisor_Site
    DROP TABLE OverTime_Report
    DROP TABLE PayPolicy
    DROP TABLE QuickComment
    DROP TABLE Region
    DROP TABLE Salary
    DROP TABLE Transfer_Positions
    DROP TABLE Transfers
    DROP TABLE UltiPro_API
    DROP TABLE [Union]
    DROP TABLE Union_Positions

    GO;

    SELECT * INTO Comments FROM srv_lnk.WorkForceManagement.dbo.Comments
    SELECT * INTO Customer_Exclude FROM srv_lnk.WorkForceManagement.dbo.Customer_Exclude WITH(NOLOCK)
    SELECT * INTO DeptStatus FROM srv_lnk.WorkForceManagement.dbo.DeptStatus WITH(NOLOCK)
    SELECT * INTO Forecast_Hourly FROM srv_lnk.WorkForceManagement.dbo.Forecast_Hourly WITH(NOLOCK)
    SELECT * INTO Forecast_Revenue FROM srv_lnk.WorkForceManagement.dbo.Forecast_Revenue WITH(NOLOCK)
    SELECT * INTO Forecast_Salary FROM srv_lnk.WorkForceManagement.dbo.Forecast_Salary WITH(NOLOCK)
    SELECT * INTO Forecast_Schedule_Change FROM srv_lnk.WorkForceManagement.dbo.Forecast_Schedule_Change WITH(NOLOCK)
    SELECT * INTO Freelance FROM srv_lnk.WorkForceManagement.dbo.Freelance WITH(NOLOCK)
    SELECT * INTO History FROM srv_lnk.WorkForceManagement.dbo.History WITH(NOLOCK)
    SELECT * INTO InvGroup FROM srv_lnk.WorkForceManagement.dbo.InvGroup WITH(NOLOCK)
    SELECT * INTO LastUpdateTables FROM srv_lnk.WorkForceManagement.dbo.LastUpdateTables WITH(NOLOCK)
    SELECT * INTO [Login] FROM srv_lnk.WorkForceManagement.dbo.[Login] WITH(NOLOCK)
    SELECT * INTO NWF_All_Employees FROM srv_lnk.WorkForceManagement.dbo.NWF_All_Employees WITH(NOLOCK)
    SELECT * INTO NWF_Closed_ACTUAL FROM srv_lnk.WorkForceManagement.dbo.NWF_Closed_ACTUAL WITH(NOLOCK)
    SELECT * INTO NWF_Closed_FORECAST FROM srv_lnk.WorkForceManagement.dbo.NWF_Closed_FORECAST WITH(NOLOCK)
    SELECT * INTO NWF_Holidays FROM srv_lnk.WorkForceManagement.dbo.NWF_Holidays WITH(NOLOCK)
    SELECT * INTO NWF_Live FROM srv_lnk.WorkForceManagement.dbo.NWF_Live WITH(NOLOCK)
    SELECT * INTO NWF_QB_Perc FROM srv_lnk.WorkForceManagement.dbo.NWF_QB_Perc WITH(NOLOCK)
    SELECT * INTO NWF_Sites FROM srv_lnk.WorkForceManagement.dbo.NWF_Sites WITH(NOLOCK)
    SELECT * INTO NWF_Supervisor_Name FROM srv_lnk.WorkForceManagement.dbo.NWF_Supervisor_Name WITH(NOLOCK)
    SELECT * INTO NWF_Supervisor_Region FROM srv_lnk.WorkForceManagement.dbo.NWF_Supervisor_Region WITH(NOLOCK)
    SELECT * INTO NWF_Supervisor_Site FROM srv_lnk.WorkForceManagement.dbo.NWF_Supervisor_Site WITH(NOLOCK)
    SELECT * INTO OverTime_Report FROM srv_lnk.WorkForceManagement.dbo.OverTime_Report WITH(NOLOCK)
    SELECT * INTO PayPolicy FROM srv_lnk.WorkForceManagement.dbo.PayPolicy WITH(NOLOCK)
    SELECT * INTO QuickComment FROM srv_lnk.WorkForceManagement.dbo.QuickComment WITH(NOLOCK)
    SELECT * INTO Region FROM srv_lnk.WorkForceManagement.dbo.Region WITH(NOLOCK)
    SELECT * INTO Salary FROM srv_lnk.WorkForceManagement.dbo.Salary WITH(NOLOCK)
    SELECT * INTO Transfer_Positions FROM srv_lnk.WorkForceManagement.dbo.Transfer_Positions WITH(NOLOCK)
    SELECT * INTO Transfers FROM srv_lnk.WorkForceManagement.dbo.Transfers WITH(NOLOCK)
    SELECT * INTO UltiPro_API FROM srv_lnk.WorkForceManagement.dbo.UltiPro_API WITH(NOLOCK)
    SELECT * INTO [Union] FROM srv_lnk.WorkForceManagement.dbo.[Union] WITH(NOLOCK)
    SELECT * INTO Union_Positions FROM srv_lnk.WorkForceManagement.dbo.Union_Positions WITH(NOLOCK)

    --COMMIT TRANSACTION
END TRY

BEGIN CATCH
    --ROLLBACK TRANSACTION
END CATCH