0

I have taken file group backup of SampleDB database and I want to restore it on to a different existing empty database RestoreDB. I was able to restore a full backup but I am having problem while restoring a file group backup.

Here is what i have tried so far.

RESTORE DATABASE RestoreDB
FROM DISK = 'D:\Sample\SampleDB_FileGroup.bak'
WITH    
    REPLACE,    -- Overwrite DB - if one exists
    NORECOVERY, -- Use if DIFFs / T/Logs to recover
--  RECOVERY,   -- Use if NO more files to recover, database will be set ready to use
    STATS = 10, -- Show progress (every 10%)
MOVE 'SampleDB' TO 'D:\Sample\RestoreDB\RestoreDB.mdf', 
MOVE 'SampleDB_2' TO 'D:\Sample\RestoreDB\RestoreDB_2.ndf',
MOVE 'SampleDB_log' TO 'D:\Sample\RestoreDB\RestoreDB_log.ldf'

Using the above code I was able to restore a full backup but I am not able to restore a file group backup.

Any ideas on how to achieve this?

Seymour
  • 7,043
  • 12
  • 44
  • 51
chaitanya.moguluri
  • 240
  • 2
  • 8

2 Answers2

0

Use the keyword PARTIAL to restore the PRIMARY filegroup and all filegruops you need, then restore the filegroup.

RESTORE DATABASE RestoreDB
FROM DISK = 'D:\Sample\SampleDB_FileGroup.bak'
WITH    
    REPLACE,    -- Overwrite DB - if one exists
    NORECOVERY, -- Use if DIFFs / T/Logs to recover
    STATS = 10, -- Show progress (every 10%)
    MOVE 'SampleDB' TO 'D:\Sample\RestoreDB\RestoreDB.mdf', 
    MOVE 'SampleDB_2' TO 'D:\Sample\RestoreDB\RestoreDB_2.ndf',
    MOVE 'SampleDB_log' TO 'D:\Sample\RestoreDB\RestoreDB_log.ldf',
    PARTIAL
rudi bruchez
  • 624
  • 3
  • 10
  • 1
    Using the keyword `PARTIAL` leaves me an error `Msg 3103, Level 16, State 1, Line 1 A partial restore sequence cannot be initiated by this command. To initiate a partial restore sequence, use the WITH PARTIAL clause of the RESTORE statement and provide a backup set which includes a full copy of at least the primary data file. The WITH PARTIAL clause of the RESTORE statement may not be used for any other purpose.` – chaitanya.moguluri Nov 13 '13 at 12:25
  • No, you are right, it won't work. You cannot restore a filegroup and move it at the same time. I add another answer to put some code in it – rudi bruchez Nov 13 '13 at 12:47
0

I tried it also:

CREATE DATABASE [SampleDB] ON  PRIMARY 
( NAME = N'SampleDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2] 
( NAME = N'F2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\F2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SampleDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

BACKUP DATABASE SampleDB
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\backup1.bak' WITH INIT

BACKUP DATABASE SampleDB FILEGROUP = N'FG2'
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\backup2.bak'

RESTORE DATABASE RestoreDB
FILEGROUP = N'PRIMARY'
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\backup1.bak'
WITH 
    MOVE N'SampleDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RestoreDB.mdf',
    MOVE N'SampleDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RestoreDB_log.ldf'    

RESTORE DATABASE RestoreDB
FILEGROUP = N'FG2'
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP\backup2.bak'
WITH 
    MOVE N'F2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RestoreDB2.ndf'

But I get an error :

The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.

I didn't know, because never had to do it, but it is not possible to move it, Paul Randal answers this one here : http://www.sqlservercentral.com/Forums/Topic412470-357-1.aspx

rudi bruchez
  • 624
  • 3
  • 10