Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

File group backup Expand / Collapse
Author
Message
Posted Saturday, January 19, 2013 4:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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
Posted Saturday, January 19, 2013 5:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
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
Posted Saturday, January 19, 2013 9:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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
Posted Saturday, January 19, 2013 7:12 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
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
Post #1409245
Posted Monday, January 21, 2013 12:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
What if i create secondary file group as read only?
Post #1409379
Posted Monday, January 21, 2013 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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
Posted Monday, January 21, 2013 6:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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
Posted Monday, January 21, 2013 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 »

Add to briefcase

Permissions Expand / Collapse