File Group Backup and Restore

Hi,
Done following things :
1. Created a database with three filegroups i.e. Primary, SalesGroup1, Sales
Group2.
2. Created a table in each of the filegroups i.e. Primarytable in Primary fi
legroup, TableGrp1 in SalesGroup1 filegroup and TableGrp2 in SalesGroup2 Fil
egroup.
3. After that you inserted two records in each of these tables.
4. So the state of each of the table after inserting the 2 records in it is
as follows :
PrimaryTable-- TableGrp1-- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
5. After this, you took the backup of FileGroup SalesGroup2 of Sales databa
se at location C:\MSSQLTESTS\Backups\FileGroup3-1.bak
6. Then you inserted the record in Table TableGrp2 with the value of 10.
7. So now, the records in all the three tables are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
8. At this state u took a transaction log backup of sales database at locat
ion C:\MSSQLTESTS\Backups\tlog-1.bak
9. Then, you inserted one more record in the Table TableGrp2 with the value
of 20. So, the records in alll the three tables are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
10. Again, at this stage you took a backup of the transaction log for sales
database at location C:\MSSQLTESTS\Backups\tLog-2.bak
11. Now, you inserted one more record in the Table TableGrp2 with the value
60. So the records in all the three tables of sales database are as follows:
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
---60
12. Finally, you took the transaction log of sales database with WITH NO_TRU
NCATE option at location C:\MSSQLTESTS\Backups\tLogtail-1.bak.
13. Now comes the main part of Restoring. First you restored the backup Fil
eGroup3-1.bak which is at location C:\MSSQLTESTS\Backups\FileGroup3-1.bak.
14. After that you restored the transaction log backup named tlog-1.bak w
hich is at location C:\MSSQLTESTS\Backups\tlog-1.bak. (The transaction log b
ackup that is taken at the stage 8)
15. Then you restored the transaction log backup named tLog-2.bak at locat
ion C:\MSSQLTESTS\Backups\tLog-2.bak. (The transaction log backup that is ta
ken at the stage 10).
16. Finally you restored the last transaction log backup of the sales datab
ase named tLogtail-1.bak that is at location C:\MSSQLTESTS\Backups\tLogtail
-1.bak. (The transaction log backup taken at the stage 12)
17. So finally after restoring all the transaction logs, when u see the reco
rds in all the three tables of the database, they are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
---60
18. But what I want is, since the first backup of the filegroup that I had t
aken is at the stage ,
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
When I will restore this database, my databases' tables should have the abov
e same records.
19. The main problem is that, in case I will make some wrong updates or ins
erts or deletion in the table TableGrp2, and then if I will restore the dat
abase in this manner then the wrong records will also be seen again in the
database table, which i
dont want. e.g. if the record of value 60 in table TableGrp2 is inserted in
the database by mistake then with restoring the database using the backed up
data will also restore the record with value 60 which I dont want.
20. I also tried restoring database without restoring the last transaction l
og backup i.e. tLogtail-1.bak. But then my database remains in the loading s
tate about which I have no idea.
Please let me know how can I solve this problem or there is any other altern
ative for this.
Also, if I am going wrong conceptully or if this method will not solve my pr
oblem then let me know.
Thanking you in advance and waiting for your reply.
Thanks and regards,
a_k93Hi,
I understand that you are trying to restore the transaction log upto a
particular point. The "Restore" command has a parameter for this.
The StopAT parameter can be used to restore the transaction upto a
particular datetime.
Restore a transaction log:
RESTORE LOG { database_name | @.database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variab
le } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY =
& #123;undo_file_name|@.undo_file_name_var}
} ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
[ [ , ] STOPAT = { date_time | @.date_time_var }
| [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ]
| [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]
]
Example :
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.