I'm working on a very large database (250+ gigs) with well over 225 million records. The database is hard to work with simply from its sheer size. This database is read-only.
We're looking at getting faster hardware, but either way I'm trying to find the most efficient way to work with the database. This database must be updated nightly from a master database and the downtime must be kept to a minimum. The master database is maintained by a third party.
I'm trying to find the best way to update the database but I'm not having a lot of luck. I looked into differential and transaction log backups but in order to apply them a full backup must be restored first. This completely defeats the purpose of a differential backup in my case, since I might as well have a full backup done on the master database and then simply restore the full backup nightly since that would be faster than restoring a fullbackup and applying the differential backups every night.
I was hoping to have a solution where I can have a full backup done once, (or maybe once a month), and then from then on simply apply some type of incremental backups based on the original full backup that build on each other. This would keep downtime to a minumun, since once the first full backup is done I would only apply the incremental backups nightly. I would simply rebuild the index after every "incremental" backup. I have not been successful in finding any solution like this.
I'm just now diving into and doing a lot of research into database backups and performance, constantly reading MSDN- however it seems this solution is not an option. I thought I would ask as a last resort- surely there are some here managing large databases where it would be impractical to do a restore nightly.
Any suggestions? I'm also open to suggestions/links to pages on performance, since I have never worked with a database quite this size.