File Group Backup and Restore

Hi
Done following things
1. Created a database with three filegroups i.e. Primary, SalesGroup1, SalesGroup2
2. Created a table in each of the filegroups i.e. Primarytable in Primary filegroup, TableGrp1 in SalesGroup1 filegroup and TableGrp2 in SalesGroup2 Filegroup
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 --
-- 2 -- 2 --
5. After this, you took the backup of FileGroup SalesGroup2 of Sales database at location C:\MSSQLTESTS\Backups\FileGroup3-1.ba
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 --
-- 2 -- 2 --
---1
8. At this state u took a transaction log backup of sales database at location C:\MSSQLTESTS\Backups\tlog-1.ba
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 --
-- 2 -- 2 --
---1
---2
10. Again, at this stage you took a backup of the transaction log for sales database at location C:\MSSQLTESTS\Backups\tLog-2.ba
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 --
-- 2 -- 2 --
---1
---2
---6
12. Finally, you took the transaction log of sales database with WITH NO_TRUNCATE option at location C:\MSSQLTESTS\Backups\tLogtail-1.bak
13. Now comes the main part of Restoring. First you restored the backup FileGroup3-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 which is at location C:\MSSQLTESTS\Backups\tlog-1.bak. (The transaction log backup that is taken at the stage 8
15. Then you restored the transaction log backup named tLog-2.bak at location C:\MSSQLTESTS\Backups\tLog-2.bak. (The transaction log backup that is taken at the stage 10)
16. Finally you restored the last transaction log backup of the sales database 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 records in all the three tables of the database, they are as follows
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 --
-- 2 -- 2 --
---1
---2
---6
18. But what I want is, since the first backup of the filegroup that I had taken is at the stage ,
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 --
-- 2 -- 2 --
When I will restore this database, my databases' tables should have the above same records.
19. The main problem is that, in case I will make some wrong updates or inserts or deletion in the table TableGrp2, and then if I will restore the database 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 log backup i.e. tLogtail-1.bak. But then my database remains in the loading state about which I have no idea.
Please let me know how can I solve this problem or there is any other alternative for this.
Also, if I am going wrong conceptully or if this method will not solve my problem 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_variable } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY ={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.