Filegroup Backup/Restore in sql server 2008

Question

Hi All,
          One of my db size is more than 200 GB so i am planning to create few filegroups for this database. I am facing one issue when i am restoring single filegroup on existing database. It gives message that filegroup has been restored successfully but when i run any query to fetch data from that filegroup, it gives error that filegroup is not not online.

          I am taking only filegroup backup not taking any log backup.

          If anyone have any document related to filegroup backup/restore then pls share it.

Thanks in advance.

Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

Answer 1

Hi Raj,

The transaction log backups are required here.

Anyway this KB article should help you to sort your problem
http://support.microsoft.com/kb/281122
http://msdn.microsoft.com/en-us/library/ms187090.aspx

---

Thanks, Leks

Answer 2

Thanks Lekss,

Is there any other way, I don't need log backup here but i want recover my database by restoring full filegroup backup.

Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

Answer 3

Hi Raj,

After the filegroup backup was taken , were there any changes to the database (especially in the filegroup for which the backup was taken) ?
If that is the case , you need to apply the log backups for this filegroup to get the filegroup online.

---

Thanks, Leks

Answer 4

I have not made any changes after filegroup backup, so i am restoring only last filegroup backup which i have taken but it gives same error as filegroup in not online.

Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

Answer 5

You need to sync that data by restoring tlog backups up to current point in time. No way around it (unless the filegroup was marked as read-only all the time since the backup was taken).

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 6

I found the following statement in http://support.microsoft.com/kb/281122 artcile. Is it not applicable in sql server 2008?


 It is not necessary to apply a transaction log backup if SQL Server can determine that the files or filegroups have not been modified after the file or filegroup backup was created


Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

Answer 7

It should applicable for SQL server 2008 as well , but are you sure that there were no transaction ? Open you trace files (if you have some for the time period under consideration)and see if that is true.
If you haven't had any transaction , then the restore operation shouldn't fail.

---

Thanks, Leks

Answer 8

I find that statement (in the KB article) very vague. Here is a statement from BOL 2008:

"If the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. This brings the filegroup forward to the log records in the current active log records in the log file. The recovery point is typically near the end of log, but not necessarily.

If the filegroup that is being restored is read-only, usually applying log backups is unnecessary and is skipped. If the backup was taken after the file became read-only, that is the last backup to restore. Roll forward stops at the target point."

Above is from http://msdn.microsoft.com/en-us/library/ms190710.aspx.
You also might want to check out http://msdn.microsoft.com/en-us/library/ms190388.aspx.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 9

Hi Tibork,
                I am agree with u, t-log restore is required here after filegroup.

Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79