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.
Best Answer
You are describing log shipping, but you want to use 'differential' backups instead of log backups, which is the problem with your approach. With log shipping you restore the database once, then apply log backups as they are being created on the principal site, and you never ever have to redo the initial full backup restore. Just keep applying the log shipped over every few hours and you'll have your read-only copy available.