Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server--piecemeal restore of filegroups from complete backup

Posted on 2014-09-22
3
Medium Priority
?
302 Views
Last Modified: 2014-09-28
We have a large database in MS SQL in which one of the tables is partitioned by a date column.  The Primary key index is also partitioned using the same partition function.  The database is kept in Simple Recovery model, since data is added to it in batches every 3 months.

DBCC checkfilegroup found consistency errors, so we needed to bring back just one filegroup from a complete backup.

Restore did not allow me to run a restore of a filegroup in Simple Mode, so I changed to full recovery mode, then ran the following, with no errors.

restore database aricases filegroup='2003'
from disk=N'backupfile-name.bak'
with recovery

I expected the "with recovery" clause to bring this back to working order, but the process ended with a note saying

The roll forward start point is now at log sequence number (LSN) 511972000001350200037. Additional roll forward past LSN 549061000001370900001 is required to complete the restore sequence.

When I query the database table that includes this filegroup I get a message saying that the primary key cannot be accessed because one of the partitions for the table cannot be access because it is offline, restoring, or defunct.

Why didn't "with recovery" clause leave this filegroup fully restored.  Now what?
The entire database is very large (1.5TB).  I can't backup the log file, because I'd first need to create a backup in full model mode.  The filegroup itself is only 300gb.

I can do the restore again-- but would like to know the correct way of performing this.
0
Comment
Question by:dakota5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40338496
I think the others filegroups are offline and you need to bring them online.
Just a simple RESTORE DATABASE aricases FILEGROUP='1', ..., FILEGROUP='n'  WITH RECOVERY will do the job,
0
 

Accepted Solution

by:
dakota5 earned 0 total points
ID: 40339084
Vitor-- thank you for your answer, but no, the other filegroups were online and could be queried.  It was the single restored filegroup that was non-functional.

I found the answer.  Bottom line is that Simple Recovery Model is very limited.  You must restore ALL read/write filegroups together from the same backup.  Individual read/only filegroups CAN be restored separately, as long as they became read/only (no more changes) BEFORE the last backup of the  read/write filegroups.

Bottom line-- only Full or Bulk-Logged models let you restore single read/write filegroups.
Bulk-Logged model is what a datawarehouse with batch loading should be using, not Simple Model.  My error in design.

see from Microsoft

http://msdn.microsoft.com/en-us/library/ms191253.aspx

then look at piecemeal restores for Simple Model
http://msdn.microsoft.com/en-us/library/ms190984%28v=sql.100%29.aspx
very limited
0
 

Author Closing Comment

by:dakota5
ID: 40348360
I found the answer documented by Microsoft and have referenced it.  I thank Vitor for his suggestion, but it was not actually correct.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question