2015 m. sausio 1 d., ketvirtadienis

MS SQL copy table to new server

/****Copy table with data from MS SQL S-1 to MSSQL S-2 ****/
/****S-1 ir S-2 yra serveriu vardai****/
/****Viskas vyksta S-2 serveryje****/
/****add link server in S-2****/
SP_ADDLINKEDSERVER
@server='S-1',
@srvproduct='',
@PROVIDER='SQLOLEDB',
@datasrc='S-1';

/****add existing user from S-1****/
SP_ADDLINKEDSRVLOGIN
@useself='FALSE',
@rmtsrvname='S-1',
@rmtuser='sa',
@rmtpassword='blablabla'
/****view linked servers in S-2****/
SELECT * FROM SYSSERVERS

/****On S-1 Database1 in Enterprise Manager script Table index and other stuff (withouth create..)****/

/****copy Table with data from S-1 to S-2****/
Select * into [S-2].[Database2].[dbo].[Table]
 from [S-1].[Database1].[dbo].[Table]
GO

/****remove linked server in S-2 (it is not necessary)****/
sp_dropserver 'S-1','droplogins'

/****run script on Table on S-2 to create index and other stuff (minus create..)****/

Happy NewYear 2015!

Komentarų nėra: