Database Reference
In-Depth Information
You can use the same sequence of actions while recovering individual files in the non-Enterpise Editions of SQL
Server, although the database switches to
RESTORING
state and would not be available to users during this process.
The same technique can be applied when you want to perform a piecemeal restore of the database, bringing it
online on a filegroup-by-filegroup basis. You could use a
RESTORE
statement, specifying the list of the filegroups, and
use the
PARTIAL
option. Listing 30-19 shows you how to perform a piecemeal restore of the
Primary
,
Entities
, and
OperationalData
filegroups.
Listing 30-19.
Piecemeal filegroup restore: Restoring Primary, Entities, and OperationalData filegroups
RESTORE DATABASE [OrderEntryDB]
FILEGROUP='Primary',FILEGROUP='Entities',FILEGROUP='OperationalData'
FROM DISK = N'V:\OrderEntryDB.bak' WITH FILE = 1,
MOVE N'OrderEntryDB' TO N'M:\OrderEntryDB.mdf',
MOVE N'OrderEntryDB_Entities' TO N'N:\OrderEntryDB_Entities.ndf',
MOVE N'OrderEntryDB_Operational' TO N'N:\OrderEntryDB_Operational.ndf',
MOVE N'OrderEntryDB_log' TO N'L:\OrderEntryDB_log.ldf',
NORECOVERY,
PARTIAL,
STATS = 5;
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB-tail-log.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;
At this point, files from the restored filegroups are online, while the historical data file is in a
RECOVERY_PENDING
state. You can see the results of the query from Listing 30-15 in Figure
30-8
.
Figure 30-8.
Piecemeal filegroup restore: Data files state after Primary, Entities, and OperationalData filegroups are restored
Finally, you can bring the
HistoricalData
filegroup online by using the
RESTORE
statements shown in Listing 30-20.
Listing 30-20.
Piecemeal filegroup restore: Restoring the HistoricalData filegroup
RESTORE DATABASE [OrderEntryDB]
FILEGROUP='HistoricalData'
FROM DISK = N'V:\OrderEntryDB.bak' WITH FILE = 1,
MOVE N'OrderEntryDB_Historical' TO N'S:\OrderEntryDB_Historical.ndf',
NORECOVERY, STATS = 5;
