Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JasonC
Starting Member
4 Posts |
 Posted - 2002-06-03 : 12:40:24
|
| Ive had a read through all the answers and articles, and Im sure the answer is there, but dont understand the terminology :-)I have SQL2000. Is good.I need to backup records. Is bad :-)I wish to copy, on a weekly basis, the records from the live site to my local SQL server.However, I dont wish to copy any more than one table and only the records that have changed from the previous week (to keep it quick even though I have dsl)What do I need to do or read for the easiest approach? |
|
|
dataphile
Yak Posting Veteran
71 Posts |
Posted - 2002-06-04 : 03:38:15
|
| 1. You could create triggers on the live database to update the local database. (Baaaaad)2. You could backup the transaction log on the live system and restore to your local system. (Best option if you want to restore the whole database, but you did say EASIEST approach)3. Obviously the tables will be of the same structure, so you could do a natural inner join to find the records that are the same. Then do a full outer join (only on the primary keys) to find the records that are not in the first result set (where t1.pk is null or t2.pk is null). Delete these records from the local table and insert from the live system. (This is the easiest, but if it sounds a bit hairy, send the field names and I will write the query for you.) |
 |
|
|
JasonC
Starting Member
4 Posts |
Posted - 2002-06-04 : 04:37:08
|
| Thanks for the info dataphile, its for a forum - so any records could change at any time. Does that change your thinking at all?Is this something where I can use a timestamp to locate changed records?Im not real familiar with the joins you mention, but if its still usable in the above situation, what does the basic framework for the SQL look like?tia |
|
|
|
dataphile
Yak Posting Veteran
71 Posts |
Posted - 2002-06-04 : 05:16:33
|
| Here I used the Northwind.dbo.orders table as an example.I created a second Orders2 which would be your local table.You will need to use the fully qualified name to address the remoted server.delete from orders2 o2a where o2a.OrderId not in(select o2.OrderID from orders o inner join orders2 o2on o.orderid = o2.orderid ando.customerid = o2.customerid ando.employeeid = o2.employeeid and...o.ever_single_field = o2.every_single_field)goinsert into orders2 o2a select o.* from orders o left outer join orders2 o2on o.OrderID = o2.OrderId where o2.OrderID is null |
|
|
|
|
|
|
|
|
Backup new records