Database Reference
In-Depth Information
log on
(name = N'OrderEntryDB_log',
filename = N'L:\OrderEntryDB_log.ldf')
In the first example, let's assume that the S: drive is corrupted and the
HistoricalData
filegroup becomes
unavailable. Let's see how you can recover the data from this filegroup and move the files to another drive.
As a first step, shown in Listing 30-14, you need to mark the corrupted file as being offline. This operation
terminates all database connections, although users can reconnect to the database immediately afterwards.
Listing 30-14.
Partial DB Availability: Mark file as offline
alter database OrderEntryDb
modify file(name = OrderEntryDB_Historical, offline)
At this point, all of the data in the
HistoricalData
filegroup is unavailable to users. However, users can still work
with the data from the other filegroups.
If you queried the
sys.database_files
view with the query shown in Listing 30-15, you would see that the data
files from the
HistoricalData
filegroup have an OFFLINE state. Figure
30-5
shows this state.
Listing 30-15.
Partial DB Availability: Querying state of the files
select file_id, name, state_desc, physical_name
from sys.database_files
Figure 30-5.
Partial DB Availability: Data files state after marking the file as offline
In the next step, you should make a tail-log backup, as shown in Listing 30-16. It does not matter that the
database is still online and that other sessions are generating log records. The
OrderEntryDB_Historical
file is offline
and, therefore, neither of the newly generated log records would apply to the data in that file. It is worth mentioning
that you should not use the
NORECOVERY
option when making a tail-log backup because
NORECOVERY
switches the
database to a restoring state.
Listing 30-16.
Partial DB Availability: Making tail-log backup
BACKUP LOG [OrderEntryDB]
TO DISK = N'V:\OrderEntryDB-tail-log.trn'
WITH NO_TRUNCATE, INIT,
NAME = N'OrderEntryDB-Tail-log backup',
STATS = 5
As a next step, you should restore a full backup from the current log chain, restoring individual files as shown in
Listing 30-17.
