How to replicate database using SQL SERVER 2000

debsoft · June 27th, 2003, 01:13 AM

Hi Friends!!

I want to replicate database in SQL SERVER 2000 by using some SQL statement and NOT by using the Backup and restore facility in SQL Enterprise Manager. How can I achieve that....any help will be appreciated....thanks

cheers,
debsoft

David Cameron · June 27th, 2003, 01:39 AM

You could add a linked server. You can do this from EM or using sp_addlinkedserver. From there you could then schedule a job that copies the data accross using a fully qualified name:
<servername>.<database name>.<owner name>.<object name> eg
INSERT INTO SQL1.db1.dbo.Table1
(Val1, Val2, Val3)
VALUES
SELECT Val1, Val2, Val3
FROM SQL2.db1.dbo.Table1

regards
David Cameron

debsoft · June 27th, 2003, 04:59 AM

Thanks David...I saw a lot of posts of yours on this site...r u a SQL Server or database expert??? Anyway keep up the good spirit...

I'll try to implement ur solution and get to know more
abt sp_addlinkedserver...

Thanks once again

cheers,
debsoft