Backup and Restore Filegroups

Hi,
I want to take the backup of a particular filegroup in SQL server 2000 and then restore the same filegroup backup.
Currently I am using the following steps :
1. Creating the database with 2 filegroups. The syntax that I used for it is as follows :
create database test
on primary
(name = test1,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test1.mdf'),
(name = test2,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.ndf'),
filegroup group2
(name = test3,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test3.ndf'),
(name = test4,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test4.ndf')
log on
(name = test_log,
filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf')

2. Now I am creating two tables table1 and table2 using Enterprise Manager. Both the Tables, table1 and table2 are on the "Group2" Filegroup of test database.

3. After creating the tables, I inserted three records in each table.

4. Then I performed the backup using the following commands:
backup log test
to disk = 'e:\test.lg1' with init

backup database test
filegroup = 'group2'
to disk = 'e:\test_group2.bak'

5. After the backup has been taken, I deleted the two records from both the tables. Now both my tables have one record each.

6. Now I tried restoring the database using following command :

backup log test
to disk = 'e:\test.lg3' with init

restore database test
filegroup = 'group2'
from disk = 'e:\test_group2.bak'
with norecovery

restore log test
from disk = 'e:\test.lg3' with norecovery

restore database test
with recovery

7. After all these commands are executed, when i checked my database, it contains again the one record in each table. Ideally, according to me the tables should contain 3 records as i had taken the backup when each table had 3 records.

How can I bring the database back to the state in which I had taken the backup.

Pls. help me as soon as possible.

And also let me know, if I need to do any SQL level or Database level settings for the above task.

Waiting for your reply. and thanking you in advance.

Regards,
a_k93A backup log beffore a backup database has no effect.

You must do a backup database, then backups log... and be sure that your option "trunc log on checkpoint " is set to false for this particular db.|||Hi,
Tried setting the option to false and followed the same backup and restore procedure after setting the option to false, but it is not working.

Can u post an example of the same, starting from the creation of the database and filegroups?

Regards,
a_k93|||do you need to recover this database as soon as possible?

unfortunately it appears that in your descirption that you have restored your full on top of your latest log backup so you should

restore the last full db backup with no recovery
restore any filegroup backups since the last full with no recovery
restore the log backups with recovery
this will force recovery to roll forward any commited xacts and roll back any incomplete xacts.
they you should be at the moment that the log backup was created.
unfortunately (if your description is correct) you will not be able to restore to the moment of corruption due to the lack of a log backup with no_truncate

good luck.

ps
download lumigent log explorer and use it to read your live log and your backed up logs and find the missing xacts and you can restore them or remove them.