April 27, 2006 at 3:13 pm
Hi,
Database Type: Microsoft SQL Server 2000
OS: Microsoft Server 2003
I would like to know if it is possible to backup a database depending on a filter. This filter would be field in a table and depending on the value it would backup the table with this value.
For example:
If I had a [Person] table and depending on the first name = 'John' I would want this to be including in the backup otherwise leave this out of the backup.
So my backup would include a table [Person] with all first names as 'John'
April 27, 2006 at 3:20 pm
This would not be possible with BACKUP. You would need to create a data extract script that could conditionally pull data from your tables.
April 27, 2006 at 3:22 pm
The standard database backup commands pick up the whole database or a whole filegroup. You could create an empty database and selectively put structures and data into it programatically based on filters, but this seems like a HUGE amount of work. What's the requirements that's driving this? The folks who are listening might have a better way to accomplish the same thing.
April 27, 2006 at 3:44 pm
Currently there is one large database but this database was made up of smaller databases (However they have the same structure - tables, fields, referential integrety, indexes, stored procedures, functions, etc)
However when it needs to be backup it will need to be backup into it's individual databases, this would all depend on a field in each table that will need to be added to show which backup it will go into.
The reason for the one database was the application that access this database can one support one access to one database.
My first idea was to create multyple databases and then thru code (like DTS) seperate all records into it's own database and then backup each database.
Something else that I am investigating is if I could have a virtual database that is a facade to many other database. So the application would think that it is looking at one database but infact it would be built upon many other database. These databases would then be easily backup.
April 27, 2006 at 6:15 pm
You could build views that join data across databases, thereby making multiple databases look like one, but I can't help believing that there's something seriously wrong here. What is the requirement that drives the need to back up subsets of the data? If you need to restore a subset, it might be easier to restore the whole thing into another database and then copy the subset back to the original, thereby dealing with the problem on the restore rather than the backup.
April 28, 2006 at 5:33 am
You could create view of the table table [Person] with all first names = 'John' , and then use the BCP program to bulk copy the data out of that view, and then set up a batch job to run it.
April 28, 2006 at 11:25 am
Thank You, For all your replies.
I will try a couple of things and hopefully post a response to this thread.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply