Transactional backup in filegroup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Transactional backup in filegroup

Transactional backup in filegroup

Rate Topic

Display Mode

Topic Options

Author

Message

MichaelJasson

Posted Thursday, May 28, 2009 4:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, Visits: 191

I have divided my database (40 GB) in many logical filegroups

-DataFilegroup1
-DataFilegroup2
-DataFilegroup3

I have done this because I want to achieve benifits of not backing up -DataFilegroup3 because this is earmarked for readonly data. Now because I take the differential backup only for DataFilegroup1 and DataFilegroup2 then what should be my stategy for taking the transactional backup? Any headsup?

-MJ

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

Post #724929

hi_abhay78

Posted Thursday, May 28, 2009 5:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, July 26, 2012 10:06 AM
Points: 267, Visits: 385

if you have kept the third file group as readonly then there is no need to take the log backup of that filegroup except once assuming there might be something in it ...rest, you can take log backups for the 2 file groups ..

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Post #724934

Grant Fritchey

Posted Thursday, May 28, 2009 6:40 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 13,378, Visits: 25,163

Logs and log backups don't have anything to do with the data file groups. If the third group is read only, then no log activity will be recorded against it. When you back up your logs, you simply back up your logs. The activity therein will only reflect the logged activity, which, if you've got one group marked as read only, will exclude that group. It's that easy.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #724985

MichaelJasson

Posted Thursday, May 28, 2009 12:10 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, Visits: 191

Thanks. I shall take the log backup for complete DB and apply the same in case of any failure. You are quite correct as when the DB is used as readonly then nothing would be logged...

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

Post #725314

« Prev Topic | Next Topic »

Permissions