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.