SQLServerWiki

“The Only Thing That Is Constant Is Change”

how to do piecemeal restore in SQL Server 2005 or later

Posted by database-wiki on October 4, 2011

ISSUE:

=====

If you have filegroup backups and log backup from customers and you just want to
restore one filegroup.

below is a quick way to do so. the key is that you need to apply “PARTIAL” keyword
during primary filegroup restore.

You may run into various errors like

Server: Msg 3116, Level 16, State 2, Line 1

The supplied backup is not on the same recovery path as the database, and is

ineligible for use for an online file restore.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Server: Msg 8653, Level 16, State 1, Line 1

The query processor is unable to produce a plan for the table or view ‘t2’
because the table resides in a filegroup which is not online.

Resolution:

=========

drop database testdb101

go

create database testdb101

on primary (name=’p’, filename=’c:\temp\p.mdf’),

filegroup fg1 (name=’file1′, filename=’c:\temp\file1.ndf’),

filegroup fg2 (name=’file2′, filename=’c:\temp\file2.ndf’)

log on (name=’log’, filename=’c:\temp\log.ldf’)

go

create table testdb101.dbo.t1(c1 int) on fg1

go

create table testdb101.dbo.t2(c1 int) on fg2

go

insert into testdb101.dbo.t1 values (1)

insert into testdb101.dbo.t2 values (2)

go

BACKUP DATABASE testdb101

FILEGROUP = ‘primary’

TO DISK = ‘c:\temp\p.bak’ with init

go

BACKUP DATABASE testdb101

FILEGROUP = ‘fg1’

TO DISK = ‘c:\temp\fg1.bak’ with init

GO

BACKUP DATABASE testdb101

FILEGROUP = ‘fg2’

TO DISK = ‘c:\temp\fg2.bak’ with init

go

backup log testdb101 to disk = ‘c:\temp\log.bak’ with init

go

drop database testdb101

go

RESTORE DATABASE testdb101

FILEGROUP = ‘primary’

FROM disk = ‘c:\temp\p.bak’

with partial, norecovery

go

RESTORE DATABASE testdb101

FILEGROUP = ‘fg1’

FROM disk = ‘c:\temp\fg1.bak’

with norecovery

go

RESTORE DATABASE testdb101

–with recovery

RESTORE log testdb101

— FILE = ‘MyDatabase_data_1’,

— FILE = ‘MyDatabase_data_2’,

— FILEGROUP = ‘fg2’

FROM disk = ‘c:\temp\log.bak’

with recovery

go

select * from testdb101.dbo.t1

Piecemeal restore is done!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: