I need to make a copy of a data table residing on one server to a table on a different server. I got into a session on machine_b and tried something like this:
SELECT * FROM machine_A.DB_Name.DBO.Table_name
But got this error message
Msg 7202, Level 11, State 2, Line 1
Could not find server 'machine_A' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
The table is not huge, If I could script out the data, I could copy and past the insert statements and that would work, but when I script the table, I just get its definition, not the data. I'm in SQL Server 2005. Any suggestions?
Thanks,
John
move data of parent table to new table
create a new file group in existing table
rebuild cluster index or create primary key into new table [in that case your new table data will move to new file group, which you have recently created]
take a file group backup
restore it on your 2nd sql instance