File backups – Readonly Files

Question

Post reply

File backups – Readonly Files

sxg6023

SSCrazy

Points: 2918
More actions
December 31, 2003 at 9:39 am

#76105

While performing backups using file groups, do we need log backups even if readonly file needs to be recovered? I meant, let us say I have a database Test with three files File1, File2 and File3. Files File1 and File2 are read only and changes are made only to File3. I perform file level backups, so File1 and File2 are backed up just once (since they are readonly) and File3 is backed up regularly. I have regular log backups for the database. Now, if File2 crashes, can I restore File2 backup and that be it OR do I need to apply the log backups (though none of the log backups would contain any transactions for that file) to bring all the three files to the same recovery level? Refer to the following statement from BOL under "Using File Backups" section:

Microsoft® SQL Server™ requires that files be recovered to a state consistent with the rest of the database. It is not possible to stop the recovery of individual files early. For this reason, you must always back up the active transaction log prior to restoring a file backup. If the transaction log is damaged or if you wish to recover the entire database to a specific point in time, you must restore the entire set of file backups before you apply transaction log backups.

The above statement does not make any reference to Readonly files, but does the log restore requirement still apply to them too. If that were the case, I am wary of file backups (in this case), since one missed log backup would make my whole backup scheme useless (unless I do periodic full backups).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

I think it does, but if you are in read only, the difference between your last full and last full + logs is nothing.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/

http://www.dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

sxg6023 SSCrazy Points: 2918 More actions · Answer 2

One of the reasons I am looking into file backups is because our database is used for data warehousing and data is loaded strictly in chronological order and never updated. Data is currently partitioned into 6 months chunks. We keep data for a certain period of time (3 years) and then drop the partition as it falls of the retention criteria. I wanted to use the file level backups, since all the read only files with inactive partitions can just be backed up once and then the current file with active partition would need to be backed up regularly. The rub here is we don't do log backups (since all data is loaded through batch). I am OK with initiating the log backups, but if file with my oldest partition fails, then I would need to restore the file backup and log backups for 2 YEARS (remember I am not planning on any full backups). This is too much risk. I could reduce the risk by doing full backups intermittantly, but since the DB is huge, I am wondering how guys do it in this case..

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

I think you'd have to do fulls periodically. At least to keep recovery.

If you do the file backups, then you can probably restore these, ignoring the logs, but it would be something to test.