We have a database with almost 1 billion rows.

90% of this table is historical data. We don't use it, BUT if we need to use it, it needs to be right here, right now (we work with banks, so, they need things pretty fast).

I'm reading about partitioning in Brent Ozar blogs etc. but I don't know if this is a good idea.

I would like to partition this table but I'm concerned about backups. If doing backups of the database, without backing up this partition, am I going to have a corrupt database in case of a restore, right?

Am I able to restore only the database, without the partitioned file?

I'm thinking about partitioning the table, throw this file created in a different disk, and just do a backup of the database without this file.

I really don't think this is a good idea, so I would like to hear about some experiences with this.

  • 1
    If you have a question about partitioning, you should ask it. if you want someone to tell you if you should use partitioning, you should hire a consultant. – Erik Darling Aug 2 '18 at 20:34
  • yes sorry I will be clearer in my question. I would like to know about backups and partitioning. I'm doing some tests here, but I will update the question. Thanks – Racer SQL Aug 2 '18 at 20:48

We have several huge log tables partitioned by dates, the typical scenario where we just use current month to store data and the rest is queried for support & troubleshooting. We run backups for the whole database, not just single partitions/filegroups as that will imply piecemeal restores later on. And you don't want to be in the middle of the night dealing with piecemeal restores, unless you are really aware of all the steps involved...I'm not. So if in doubt, use partition at will, analyze your scenario to find the best partition function and run a normal backup of the whole database.

| improve this answer | |
  • Good thank you for this information. Do you store the old data from the historic table in a different disk? Oh , and Monthly, you remove your old data, and keep only the current month? do you do this with the function? – Racer SQL Aug 7 '18 at 19:06
  • No, we keep full year, the partition function allows to do all querying on current month and all maintenance jobs run on the other partitions without issues. We have jobs in place that move historic data (older than 1 year) on a daily basis to a different database on a different drive. Our support team knows that if they need to query data older that one year, they need to query a different database. – Yaroslav Aug 8 '18 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.