December 31, 2003 at 9:39 am
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).
December 31, 2003 at 10:35 am
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
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
January 2, 2004 at 12:59 pm
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.
January 2, 2004 at 1:10 pm
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.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply