April 27, 2010 at 8:25 pm
Hi Everyone,
I have been trying learn SQL Server by myself from quite some time. While today when I was trying to work on Backup / Restore scenario, i stumbled upon one question ... to explain my problem, let me first tell you guys what i am trying to do here.
I have created three filegroups ... primay,FG1 and FG 2 in my DB (AWSample) ... and FG1 is default Filegroup & it contains one dummytable with some data and FG2 is empty. Now, as you know we can restore our database at any given point if we have both Full Backup file and unbroken Log Backup chain. So My Question is, Is is possible to restore database (or say just one filegroup which acutally contains data) to ANY given point just same as we can do with combination of Full Backup+LogBackup ???
I found that without restoring TAIL LOG Backup, we can't bring Filegroup which is changed (in my case FG1). I have made following code to use for this purpose,
--- For Full Backup
BACKUP DATABASE AWSample
TO DISK = 'E:\BackupData\AWData1.BAK'
-- Changed some data
BACKUP LOG AWSample
TODISK='E:\BackupData\AWLOG_1.trn'
WITH INIT
-- Assume user delted something that he shouldn't do
BACKUP LOG AWSample
TODISK='E:\BackupData\AWLOG_Tail.trn'
WITH INIT
--- So I start restoring my DB
RESTORE DATABASE AWSample
FROM DISK='E:\BackupData\AWSData1.bak'
WITH NORECOVERY
-- And I just restore till 1st Log backup as it has data which I am want
RESTORE LOG AWSample
FROM DISK 'E:\BackupData\AWLog_1.trn'
WITH NORECOVERY
RESTORE DATABASE AWSample
WITH RECOVERY
---- DO so same above process For File Group
-- I am taking backup of filegroup FG1
BACKUP DATABASE AWSample
FILEGROUP='FG1'
TODISK = 'E:\BackupData\AWSFG1.BAK'
-- Changing some data
BACKUP LOG AWSample
TODISK='E:\BackupData\AWLog_2_1.trn'
WITH INIT
GO
-- Again assume user delted something that he shouldn't do :-D
-- So I take TAIL LOG Backup
BACKUP LOG AWSample
TODISK='E:\BackupData\AWLog_2_TailLog.trn'
WITH INIT
GO
-- And start to restore DB
RESTORE DATABASE AWSample
FILEGROUP='FG1'
FROM DISK='E:\BackupData\AWSFG1.bak'
WITH NORECOVERY
-- Now, Transaction Log Backup 1 has all what I need
RESTORE LOG AWSample
FROM DISK 'E:\BackupData\AWLog_2_1.trn'
WITH NORECOVERY
-- But untill I restore this Tail Log Backup, I wont be able to access FG1
RESTORE LOG AWSample
FROM DISK 'E:\BackupData\AWLog_2_TailLog.trn'
WITH NORECOVERY
RESTORE DATABASE AWSample
WITH RECOVERY
So, am I missing something here ?? Or With help of Filegroup Backups we just cant restore database into any point in time ???
I will really appriciate if you have any suggestiongs.
Thanks,
Jack
May 12, 2010 at 9:47 pm
Wow, So many views, yet not a single reply !!!
I guess, I am on my own in this quest 🙁
February 7, 2013 at 6:47 am
i'm not that clear with filegroups, but is it so critical to use filegroups in db backup? Isn't it better to work with whole db?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply