SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Backup all tables updated since a date?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/07/2013 :  06:27:59  Show Profile  Reply with Quote
Anyone know a good way to backup tables updated since xx/xx/xx on Server1.DB1, and restore offsite onto Server2.DB1 ?? Bear in mind the db on server1 has been fully backed up multiple times since xx/xx/xx, and is also a log-ship primary.

I think this can be done on 2K5 and onwards, but SS2K ?

Cheers,

JB

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 06/07/2013 :  06:36:29  Show Profile  Visit webfred's Homepage  Reply with Quote
Isn't backup always on database and not on table?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/07/2013 :  06:41:26  Show Profile  Reply with Quote
do you mean the tables are on a separate filegroup?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/07/2013 :  06:52:38  Show Profile  Reply with Quote
Hi guys, I'll give you a fuller overview.

We keep an offsite copy of a live DB. That copy is queried and retained as needed, and typically dropped afer a few weeks/months. We have a procedure in place where we will refresh by backing up the ENTIRE live DB, copying the .bak file to the offsite server, dropping the existing instance of that db (if any), restoring (with replace if need be) from the .bak file.

Now, for reasons beyond my control, space on that offsite server is very restricted, so it would likely be easier (and faster!) to copy ONLY those tables updated on Server1.DB1 since Server1.DB2 was created, and then restore.

If feasible, I can go ahead and create filegroups - but IS it feasible? And how??

Cheers,


JB
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/07/2013 :  07:01:16  Show Profile  Reply with Quote
In that case isnt it better to drop and recreate the changed tables alone from live server to offsite copy. Of course you've to determine relationships like fks,constraints etc on them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/07/2013 :  07:10:07  Show Profile  Reply with Quote
I don't usually deal with DB structures, what do you suggest is the best way to determine those relationships, and how do I script it all out??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/07/2013 :  07:30:02  Show Profile  Reply with Quote
use catalog views for that like INFORMATION_SCHEMA.TABLE_CONSTRAINTS,CONSTRAINT_COLUMN_USAGE etc
you'll need a control table in any case to capture tables that need to be backed up and applied to offsite server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/07/2013 :  07:55:24  Show Profile  Reply with Quote
What are catalog views/Control tables? Aren't those only available in 2K5? Again, I'm very new to this dev-type stuff.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/07/2013 :  08:03:00  Show Profile  Reply with Quote
quote:
Originally posted by JaybeeSQL

What are catalog views/Control tables? Aren't those only available in 2K5? Again, I'm very new to this dev-type stuff.


yep..they're
see

http://msdn.microsoft.com/en-us/library/ms186778(v=sql.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/07/2013 :  08:15:36  Show Profile  Reply with Quote
Ran the INFORMATION_SCHEMA.TABLE and INFORMATION_SCHEMA.CHECK_CONSTRAINTS views, no check constraints apply. But what about FK's and whatever else I'll need?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/09/2013 :  04:05:10  Show Profile  Visit russell's Homepage  Reply with Quote
Are you looking for tables whose definition has changed, or whose data has changed?

Edited by - russell on 06/09/2013 04:05:38
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/09/2013 :  11:55:45  Show Profile  Reply with Quote
V
quote:
Originally posted by JaybeeSQL

Ran the INFORMATION_SCHEMA.TABLE and INFORMATION_SCHEMA.CHECK_CONSTRAINTS views, no check constraints apply. But what about FK's and whatever else I'll need?


it should be in INFORMATION_SCHEMA.REFERENTIAL_CONTRAINTS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/10/2013 :  05:33:01  Show Profile  Reply with Quote
quote:
Originally posted by russell

Are you looking for tables whose definition has changed, or whose data has changed?



Hi Russell, it's changed or new data I'm looking for, not metadata. However my concerns are both how to locate it, how to back it up (given that this is a log-ship primary) and finally, how to restore it (given whatever FK's or other 'gotcha's I find).

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/10/2013 :  08:14:02  Show Profile  Reply with Quote
quote:
Originally posted by JaybeeSQL

quote:
Originally posted by russell

Are you looking for tables whose definition has changed, or whose data has changed?



Hi Russell, it's changed or new data I'm looking for, not metadata. However my concerns are both how to locate it, how to back it up (given that this is a log-ship primary) and finally, how to restore it (given whatever FK's or other 'gotcha's I find).




If you want the actual data before and after changed, you should be using INSERTED and DELETED tables inside a trigger to get it and not metadata views.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 06/10/2013 :  09:42:37  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by JaybeeSQL

quote:
Originally posted by russell

Are you looking for tables whose definition has changed, or whose data has changed?



Hi Russell, it's changed or new data I'm looking for, not metadata. However my concerns are both how to locate it, how to back it up (given that this is a log-ship primary) and finally, how to restore it (given whatever FK's or other 'gotcha's I find).




If you want the actual data before and after changed, you should be using INSERTED and DELETED tables inside a trigger to get it and not metadata views.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





Can these be temp tables? Similarly I will need to drop and recreate the trigger.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000