Designing a Backup Strategy - Pro SQL Server Internals - page 602

Listing 30-17. Partial DB Availability: Restoring a full backup
RESTORE DATABASE [OrderEntryDB]
FILE = N'OrderEntryDB_Historical'
FROM DISK = N'V:\OrderEntryDB.bak'
WITH FILE = 1,
MOVE N'OrderEntryDB_Historical'
TO N'P:\OrderEntryDB_Historical.ndf',
NORECOVERY , STATS = 5
If you ran a query that shows the state of the files from Listing 30-15 again, you would see the results shown in
Figure 30-6 . Only one file would be in the RESTORING stage, while all other files are online and available to users.
Figure 30-6. Partial DB Availability: Data files state after applying a full backup
Finally, you should restore all other differential and log backup files, finishing with the tail-log backup. You do not
need to specify each individual file here. SQL Server will restore only files that are in the RESTORING state. Review
the code for doing this, as shown in Listing 30-18.
Listing 30-18. Partial DB Availability: Restoring other backup files
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
-- Restoring tail-log backup
RESTORE LOG [OrderEntryDB]
FROM DISK = N'V:\OrderEntryDB-tail-log.trn'
WITH FILE = 1, NORECOVERY, STATS = 5;
RESTORE DATABASE [OrderEntryDB] WITH RECOVERY;
The database is recovered, and all files are now online, as shown in Figure 30-7 .
Figure 30-7. Partial DB Availability: Data files state after restore