January 19, 2013 at 4:03 am
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
January 19, 2013 at 5:54 am
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.
January 19, 2013 at 9:52 am
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
January 19, 2013 at 7:12 pm
Gail is right. You will have to restore to two separate databases, each rolled forward to different point-in-time.
January 21, 2013 at 12:46 am
What if i create secondary file group as read only?
January 21, 2013 at 2:46 am
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
January 21, 2013 at 6:04 am
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?
January 21, 2013 at 6:40 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply