File group backup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » File group backup

File group backup

Rate Topic

Display Mode

Topic Options

Author

Message

IT researcher

Posted Saturday, January 19, 2013 4:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

I am new to file group backup and restore .So i am trying something to do with file groups.
Below i have shown some steps which i did one by one. Please ask me if anyone don't understand the steps what i did!!

I have taken following backup

Primary fg backup--pri1.bak

secondary fg backup--sec1.bak

log backup--log1.bak

Inserted some rows to table of primary file group.

Inserted some rows to table of secondary file group.

primary fg backup--pri2.bak

secondary fg backup-- sec2.bak

log backup--log2.bak

What i wanted to acheive is that after all restoration i want all table of primary file group with all data(data which is present in both pri2.bak) and secondary file group tables with the data containing only in sec1.bak i don't want the data which i have inserted to secondary file group tables after taking sec.bak1.So i did following steps.

RESTORE DATABASE [test23] FILE = N'nvt' FROM DISK = N'D:\pri2.bak' WITH NORECOVERY

GO

RESTORE DATABASE [test23] FILE = N'nvt_fg' FROM DISK = N'D:\sec1.bak' WITH NORECOVERY GO

RESTORE LOG [test23] FROM DISK = N'D:\log1.bak' WITH NORECOVERY

GO

RESTORE LOG [test23] FROM DISK = N'D:\log2.bak' WITH RECOVERY

GO

But when log2.bak restored it recovers all data of secondary file group.If i don't restore log2.bak then database will not be recovered. So how can achieve what i have mentioned above ?

Thank you

Post #1409175

Jayanth_Kurup

Posted Saturday, January 19, 2013 5:54 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

I am pretty sure you dont get an option to backup the log file in file and file group backup.
Could you confirm the exact steps you performed.

What your trying to do is something like a piecemeal restore. where only certain file groups are brought online but in order to do this the filegroups that arent brought online should be read only filegroups.

Jayanth Kurup

Post #1409192

GilaMonster

Posted Saturday, January 19, 2013 9:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

As I mentioned previously (www.sqlservercentral.com/Forums/Topic1407762-391-1.aspx)

You'd have to restore the primary filegroup backup, the the secondary, then all the log backups since the oldest of those 2 backups to bring the DB to a consistent point

You can't restore parts of a database to one time and parts to some other time. A database has to be all at one point in time.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1409210

Vedran Kesegic

Posted Saturday, January 19, 2013 7:12 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 343, Visits: 1,077

Gail is right. You will have to restore to two separate databases, each rolled forward to different point-in-time.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1409245

IT researcher

Posted Monday, January 21, 2013 12:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

What if i create secondary file group as read only?

Post #1409379

GilaMonster

Posted Monday, January 21, 2013 2:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

If it was read only at the time of backup, then when you restore it, you won't need to roll the logs forward, as it was read only and hence could not have changed.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1409440

IT researcher

Posted Monday, January 21, 2013 6:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 186, Visits: 956

If i create a file group with read only then how can i insert the values to the table? What is the use of read only file group?

Post #1409521

GilaMonster

Posted Monday, January 21, 2013 6:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

winmansoft (1/21/2013)

If i create a file group with read only then how can i insert the values to the table?

You can't. That's what read only means.

What is the use of read only file group?

Historical data. Static lookups. Anything that doesn't change.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1409531

« Prev Topic | Next Topic »

Permissions