March 15, 2005 at 4:45 am
I have two file groups . Some of my database tables are on file group1 and Some are onfile group 2 . I backed up the filegroup 1 on One day and file group 2 on another day and I have tranasction log for DB after taking filegroup backups. Now One of my data file is
corrupted. I want to restore only corrupted file or file group . When I tried to restore file group from enterprise manager
using file/File group option . I am getting this error
"The tail of the log for database "DBName" has not been backed Up.Backup the log and rerun the restore statement specifying the FILE Clause. Restore Database is terminating abnormally.
Anyone has any idea what is wrong ?
Khalid Abbasi
March 15, 2005 at 5:29 am
You will have to take a Transaction Log backup of the database before restoring any file/filegroup backup on the database. This is the normal behaviour of Sql Server 2k.
Also you need to specify the proper syntax when restoring. Here's a example :
USE master
GO
-- Restore the files and filesgroups for MyNwind.
RESTORE DATABASE MyNwind
FILE = 'MyNwind_data_1',
FILEGROUP = 'new_customers',
FILE = 'MyNwind_data_2',
FILEGROUP = 'first_qtr_sales'
FROM MyNwind_1
WITH NORECOVERY,
REPLACE
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
Let me know of you progress. But dont forget to take a T-Log backup of the database before starting the File/Filegroup recovery.
--Kishore
March 15, 2005 at 12:01 pm
thanks Kishore
It works !I was not aware of to take a T-Log backup of the database before starting the File/Filegroup recovery. I thought that It is not possible to take a TLog backup of corrupted database.
Now I have another Scenario, Can you please answer this
I have two File groups say filegroupA and filegroupB . both file group have
one database file. say data1 on filegroupA and data2 on filegroupB. I have few huge tables that I distribute between two filegroups say table1 on filegroup1 and table2 on filegroup2 . I take the backups(filegroup backup) of each file group on alternative days and transaction log of database every 3 hours. Some how one of my table say table2 on file group2 corrupted or deleted. I want to restore only that table2 from filegroup2 (which has only table2).
Now If I take the TLog before start filegroup restoration. I will loose the table2. Is there any way to restore table2 from filegroup2.
March 15, 2005 at 12:02 pm
thanks Kishore
It works !I was not aware of to take a T-Log backup of the database before starting the File/Filegroup recovery. I thought that It is not possible to take a TLog backup of corrupted database.
Now I have another Scenario, Can you please answer this
I have two File groups say filegroupA and filegroupB . both file group have
one database file. say data1 on filegroupA and data2 on filegroupB. I have few huge tables that I distribute between two filegroups say table1 on filegroup1 and table2 on filegroup2 . I take the backups(filegroup backup) of each file group on alternative days and transaction log of database every 3 hours. Some how one of my table say table2 on file group2 corrupted or deleted. I want to restore only that table2 from filegroup2 (which has only table2).
Now If I take the TLog before start filegroup restoration. I will loose the table2. Is there any way to restore table2 from filegroup2.
March 15, 2005 at 12:03 pm
thanks Kishore
It works !I was not aware of to take a T-Log backup of the database before starting the File/Filegroup recovery. I thought that It is not possible to take a TLog backup of corrupted database.
Now I have another Scenario, Can you please answer this
I have two File groups say filegroupA and filegroupB . both file group have
one database file. say data1 on filegroupA and data2 on filegroupB. I have few huge tables that I distribute between two filegroups say table1 on filegroup1 and table2 on filegroup2 . I take the backups(filegroup backup) of each file group on alternative days and transaction log of database every 3 hours. Some how one of my table say table2 on file group2 corrupted or deleted. I want to restore only that table2 from filegroup2 (which has only table2).
Now If I take the TLog before start filegroup restoration. I will loose the table2. Is there any way to restore table2 from filegroup2.
March 15, 2005 at 12:04 pm
thanks Kishore
It works !I was not aware of to take a T-Log backup of the database before starting the File/Filegroup recovery. I thought that It is not possible to take a TLog backup of corrupted database.
Now I have another Scenario, Can you please answer this
I have two File groups say filegroupA and filegroupB . both file group have
one database file. say data1 on filegroupA and data2 on filegroupB. I have few huge tables that I distribute between two filegroups say table1 on filegroup1 and table2 on filegroup2 . I take the backups(filegroup backup) of each file group on alternative days and transaction log of database every 3 hours. Some how one of my table say table2 on file group2 corrupted or deleted. I want to restore only table2 from filegroup2 (which has only table2).
Now If I take the TLog before start filegroup restoration. I will loose the table2. Is there any way to restore table2 from filegroup2.
March 16, 2005 at 2:36 am
I guess you cannot recover the table2 from the filegroup as the filegroup recovery will need a T-Log backup. If you take a T-log backup, that will also contain the deleted data. I am not sure if you will be able to recover your data.
If you still manage to recover your data, I would also like to know the steps you followed.
--Kishore
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply