Backup and Restore Filtered Data from SQL Server database using C#

views:

378

answers:

Backup and Restore Filtered Data from SQL Server database using C#

Hello Friends,

I wish to implement backup and restore feature for my application. Here I want to backup filtered data(not the whole database).

Like Select * from Sales where CompanyId=1 For all tables in database and write these data to a file.bak file, later which I can be used for restore purpose.

My Question here is Is there any way to implement this feature using SMO? If you have any other suggestion about how to implement this, I am very happy to hear it.

Please help me friends..

+2 A:

There is no native way in which you are going to achieve this backup, but there are some awkward workarounds you can do to try to get this functionality.

If every table includes the CompanyId field, you could create a partition schema / function based on the company Id, and specifically place each partition of the schema on to a separate file group. This has then split the data for each CompanyId onto a different file group, which is the key since there is the functionality to perform a file / file group level backup in SQL instead of the entire database.

I wouldn't do this unless it was the last option, I think I would work out exactly what the backup / restore requirements are, and check whether there are better options / choices.

Andrew 2009-12-21 11:18:49

Andrew, thanks for your help.So I should use a partition function here.I need an advise here. Should I create filegroup for each company? Will it give more performance? or just perform partition at the time of backup?I think first option will be good. but I need to create dynamic partition function which updates on each company creation, and this will cause re-indexing .

Sasikumar D.R. 2009-12-21 13:08:10

I am new to this partitioning and things. Till now I have only worked with Select,insert/update/delete commands.. I am trying to understand these things. Is it correct, what I said on previous comment?

Sasikumar D.R. 2009-12-21 13:12:46

Performance - depends how it is set up and the IO, you will gain partition elimination in the queries, as long as every query actually specifies the partition key in a where clause, so some will gain but probably not all. Insert / Update / Select all work as normal, it is invisible from that perspective.The partition creation / maintenance is the bigger issue - and the addition of a new partition does not cause re-indexing, if you use aligned indexes - which means the index is partitioned on the same scheme.

Andrew 2009-12-21 14:04:01

hmm.. thanks for your help.. I will try on working partition and create filegroup for each Compnay. this is help for backup operation also.

Sasikumar D.R. 2009-12-21 14:25:14

Another question here. Suppose I have 3 companies Installed and 3 file groups here, partition function will have 3 range splitters. When I tried to add new company, I will alter partition function and alter table with this partition function. Then, will the data splitting start from scratch? or only data going to insert will affect?

Sasikumar D.R. 2009-12-21 14:32:55

If you perform a split on a partition function range and effectively split an existing range of data, it will move data - this is a bad idea as a rule, it is an exceptionally expensive operation IO and CPU wise for larger quantities.you are trying to do multi-tenancy using partitioning, there are a lot of problems with that approach see http://stackoverflow.com/questions/1940272/multitenancy-and-partitaioning/1940337#1940337 for more 'cons'

Andrew 2009-12-21 14:36:24

ansaurus

tags:

views:

answers:

Backup and Restore Filtered Data from SQL Server database using C#

related questions