SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File group backup


File group backup

Author
Message
IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 1798
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
Jayanth_Kurup
Jayanth_Kurup
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5841 Visits: 1351
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232302 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2056 Visits: 1266
Gail is right. You will have to restore to two separate databases, each rolled forward to different point-in-time.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 1798
What if i create secondary file group as read only?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232302 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


IT researcher
 IT researcher
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1710 Visits: 1798
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232302 Visits: 46356
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search