Solved

Restoring filegroups in MS SQL 2008

Posted on 2011-03-24
12
1,017 Views
Last Modified: 2012-05-11
Hi,

I would like to know how to restore specific Filegroups from a a filegroup backup file in sql server 2008.

i have followed books online's tutorial step by step, yet nothing seems to work.

This is the exception i cannot seem to get figured out...

File 'Tester2' was not backed up in file 1 on device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak'. The file cannot be restored from this backup set.
RESTORE DATABASE is terminating abnormally. (.Net SqlClient Data Provider)


Any help would be a life saver,

Thank you in advance.
0
Comment
Question by:wilcosw
[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
  • 5
  • 5
  • 2
12 Comments
 
LVL 8

Expert Comment

by:avirups
ID: 35205714
Please run the following and post the result here:

RESTORE HEADERONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak'
WITH NOUNLOAD;
GO
0
 
LVL 1

Author Comment

by:wilcosw
ID: 35205874
Hi avirups,

After i ran the above query, this is the result i got...

Please see the attached file.

 QueryResult.xlsx
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35206513
Check files in this particular backup:

RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:wilcosw
ID: 35206623
Hi,

I 'm not exactly sure what the above query is supposed to retrieve ?

Please check my original question .
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35206696
I would like you to assure whether your file 'Tester2' is in the backup, look for LogicalName column.
Above query retrieves file list contained in a backup.
0
 
LVL 8

Expert Comment

by:avirups
ID: 35207709
The result you posted confirms that the backup was a file type backup. If you run

RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak'

as rightfully suggested by Daniel_PL, you will get the details of what files are backed up within this backup file and whether or not Tester2 is a part of this backup. If the error you are getting is to be believed then you shall not find the Tester2 in your result of the above query.

In that case you'll have to find out the backup file that contains Tester2 in order to restore it.

Hope this helps.
0
 
LVL 1

Author Comment

by:wilcosw
ID: 35213207
Hi,

Sorry for my delayed reply.

I have attached the results for the above mentioned query, and the file tester2 does appear in the retrieved results. QueryResult-FileListOnly.xlsx
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35213218
Can you post command you use to restore which gives you error?
0
 
LVL 1

Author Comment

by:wilcosw
ID: 35213241
Hi,

I didn't use t-sql, i used ssms for both creating the backup and restoring the backup.

Would using t-sql result in possible different behaviour?
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35213595
SSMS seems to don't allow restore single file/filegroup from full backup. You can script your actions by choosing all options and then using the script button at the top of the window.

In your case you have full backup, you can restore your Tester2 filegroup by executing command:
 
RESTORE DATABASE Tester 
FILEGROUP = N'Filegroup1' 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak' WITH STATS = 10
GO

Open in new window


You can execute only file/filegroup backup (backup will only contain this file/filegroup):
 
BACKUP DATABASE Tester 
FILEGROUP = N'Filegroup1' 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Filegroup1.bak'
GO

Open in new window


Enetrpise edition has great feature called piecemeal restore:
http://msdn.microsoft.com/en/library/ms177425%28v=sql.100%29.aspx
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35213622
Forgive me my forgetness :)
Please append ,RECOVERY to the end of first script:

RESTORE DATABASE Tester 
FILEGROUP = N'Filegroup1' 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak' WITH STATS = 10, RECOVERY
GO

Open in new window

0
 
LVL 1

Author Comment

by:wilcosw
ID: 35214847
Hi,

Thank you very much for your help and advice.

I was able to restore my filegroups using the following set of commands.....(please see code insert at bottom).

One thing i noted was that i had to create a log backup after i made my last backup of a filegroup, before i was able to restore successfully.
This had to be done in order to sync the database log sequence numbers (lsn) after the individual filegroup restores.

 
RESTORE DATABASE Tester
   FILE = 'Tester',
   FILEGROUP = 'Primary'
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Primary.bak'
   WITH REPLACE
GO

RESTORE DATABASE Tester
   File = 'Tester2',
   Filegroup = 'Filegroup1'
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Filegroup1.bak'
   WITH NOUNLOAD, REPLACE, RECOVERY
GO


-- Apply the last transaction log backup.
RESTORE LOG Tester
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\Log_Full.bak'
   WITH REPLACE,RECOVERY
GO

Open in new window

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
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…

615 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