I use sql server 2008r2 and have a large database. Can i make a partial backup? If can, how to do this?
It would help to know why do you want to make a partial backup / i.e. what problem you are trying to solve.
Because database is large, i don't want to backup whole database. I would like to backup only important data. Is it possible?
If the database is separated using file groups. you can backup each file group as alone
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)
1 Like
Thanks to all for useful replies.