Sql-server – How to properly restore database from file groups backups

restoresql server

I have very (from my point of view) strange error while trying to restore SQL server DB from a series of filegroup backups.

Here is my corrent config:

I have DB with 2 files 2 filegroups and 1 log file.
1st group (called PRIMARY is not read-only and contains my current data).
2nd is (called HISTORICAL) is read-only.

I also have partitioning.

Because of lack of space I make following:

  1. After some service operations (moving data from PRIMARY to HISTORICAL FG) I make FULL Database backup (each month).
  2. After that make FULL backup of both groups and then I make log backup.
  3. I make PRIMARY FG full backup each night.
  4. I make PRIMARY FG differential backup each hour.
  5. I make t-log backup every 15 minutes.

My strategy is to minimize space required for backups.

In case of faliour I wil restore FULL backup from beginning of the month. After that I will restore HISTORICAL backup (which is only one for each month). After that I will restore full PRIMARY FG backup from desired time (I am not sure if I have to restore PRIMARY FG backup after FULL DB backup). As last step I will restore t-log backup and return DB in operational state (if needed).

I do that operations in the past. Right now I receive error that the LSN that I tried to restore is too recent. Why this happends?

Best Answer

The sequence may be a bit off for a piecemeal restore you're attempting, in particular when to apply the tlogs. This is what TechNet has documented.

Piecemeal Restore Under the Full Recovery Model

Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time. The restore sequences of a piecemeal restore behave as follows:

•Partial-restore sequence

The partial restore sequence restores the primary filegroup and, optionally, some of the secondary filegroups.

The first RESTORE DATABASE statement must do the following:

◦Specify the PARTIAL option. This indicates the start of a piecemeal restore.

◦Use any full database backup that contains the primary filegroup. The common practice is to start a partial restore sequence by restoring a partial backup.

◦To restore to a specific point in time, you must specify the time in the partial restore sequence. Every successive step of the restore sequence must specify the same point in time.

•Filegroup-restore sequences bring additional filegroups online to a point consistent with the database.

Related Question