SQLTeam.com | Weblogs | Forums

Partial backup


#1

I use sql server 2008r2 and have a large database. Can i make a partial backup? If can, how to do this?


#2

It would help to know why do you want to make a partial backup / i.e. what problem you are trying to solve.


#3

Because database is large, i don't want to backup whole database. I would like to backup only important data. Is it possible?


#4

If the database is separated using file groups. you can backup each file group as alone


#6

The main problem is retaining integrity of the data - some tables backed up, some not.

You can make a Partial Backup, but that will only exclude File Groups that are read-only - so if you have read-only data in your database you could organise it into read-only filegroups and then exclude them from the backup. But I don't think you can exclude any read-write tables from a backup.

My approach is to put "unnecessary data" in a separate database, and back that up differently. Example: we have transient data which is imported from another system. Its huge, we have to import all the columns in all the tables, and then we only actually use a few columns from that - so that "staging data" goes into a separate database, it is set to SIMPLE Recovery Model (we can easily recreate the data by running a fresh "import" from the remote system), whereas the main database is set to FULL Recovery Model.

You can replace a Table, in the main database, with a VIEW which does a SELECT on the (original) table which is (now) in the "other database", so existing code will continue to run just fine (although beware of code that does Insert / Update / Delete - but if you have trouble with that you can put an INSTEAD OF trigger on the View to handle it - again, without having to change existing code)


#7

Thanks to all for useful replies.