0

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..

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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 .
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?
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.
hmm.. thanks for your help.. I will try on working partition and create filegroup for each Compnay. this is help for backup operation also.
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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.