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.


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.


Perhaps this third party would set up some kind of replication to move the changes over nightly?

  • Yes- this is a good suggestion. However, I'm not sure if this will be a possibility. I am afraid this may be the only option, though. There isn't any other way to do this, is there?
    – KTF
    Jan 25 '10 at 15:55
  • This is the only way I know of.
    – Sam
    Jan 25 '10 at 18:58

If you're allowed access to the environment which houses the production database & the master and read only databases can be the same database instance running SQL2005 / SQL2008 Enterprise edition you can use database snapshots. This will give you an instant point in time read-only copy of the database.


If you're not allowed access to the Production environment you could ask if they're willing to set up a mirrored database on your environment - this will also allow you to run a snapshot however you'll need Enterprise edition software and licenses.


If they're / you're not using Enterprise or you need near live data then transactional replication is another option.


If your restore is taking too long consider the option of purchasing backup disk compression software - this usually speed up the backups / restores by a factor depending upon the type of data held.


Remus answered it first but here is how the log shipping scenario would work:

  1. third party sends you a full backup of the database on 1/1
  2. You restore the database in norecovery with standby mode, this sets the database up as read only (to dbos only)
  3. Third party sends you a transaction log backup (or several) on 1/2 and you apply them to the database, bringing the changes up to date. After the backup is applied the database will be in standby mode again, read only
  4. Repeat process daily

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.