| Author |
Topic |
|
JaybeeSQL
Posting Yak Master
112 Posts |
 Posted - 2013-06-07 : 06:27:59
|
| 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-06-07 : 06:36:29
|
Isn't backup always on database and not on table?
Too old to Rock'n'Roll too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 06:41:26
|
| do you mean the tables are on a separate filegroup?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-07 : 06:52:38
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 07:01:16
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-07 : 07:10:07
|
| 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?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 07:30:02
|
| use catalog views for that like INFORMATION_SCHEMA.TABLE_CONSTRAINTS,CONSTRAINT_COLUMN_USAGE etcyou'll need a control table in any case to capture tables that need to be backed up and applied to offsite server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-07 : 07:55:24
|
| What are catalog views/Control tables? Aren't those only available in 2K5? Again, I'm very new to this dev-type stuff. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 08:03:00
|
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'reseehttp://msdn.microsoft.com/en-us/library/ms186778(v=sql.90).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-07 : 08:15:36
|
| 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? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-06-09 : 04:05:10
|
| Are you looking for tables whose definition has changed, or whose data has changed? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-09 : 11:55:45
|
Vquote:
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-10 : 05:33:01
|
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). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-10 : 08:14:02
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2013-06-10 : 09:42:37
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Can these be temp tables? Similarly I will need to drop and recreate the trigger. |
|
|
|
|
Backup all tables updated since a date?