Recently I had a few questions about how to go about partial restores if you have media failure that affects only specific filegroups, and your database is inaccessible because of the missing file(s). The steps while simple, are not readily apparent in example form in books online.
In this example you have a multi filegroup database in bulk_logged recovery model. The database consists of read/write and read only filegroups. I throw these factors in to add a little bit more variance to the example.
-- create example database and filegroups
CREATE DATABASE my_db
go
ALTER DATABASE my_db
SET RECOVERY bulk_logged
Go
alter database my_db add filegroup fg_static_A;
alter database my_db add filegroup fg_static_B;
alter database my_db add filegroup fg_static_C;
alter database my_db add filegroup fg_readwrite_A;
go
alter database my_db add file (name = 'f_static_A', filename = 'C:\f_static_a.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_A;
alter database my_db add file (name = 'f_static_B', filename = 'C:\f_static_b.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_B;
alter database my_db add file (name = 'f_static_C', filename = 'C:\f_static_c.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_static_C;
alter database my_db add file (name = 'f_readwrite_A', filename = 'C:\f_readwrite_A.ndf', size = 2 ,filegrowth = 2 MB ) to filegroup fg_readwrite_A;
You have done some bulk loads of static data to partitions in specific filegroups. You then mark these filegroups as read only:
ALTER DATABASE my_db
MODIFY FILEGROUP fg_static_A READ_ONLY
ALTER DATABASE my_db
MODIFY FILEGROUP fg_static_B READ_ONLY
Your next scheduled full backup occurs. The full backup captures both read only and read/write filegroups:
BACKUP DATABASE my_db
TO DISK='C:\my_db_full.bak' WITH INIT
You then do some more bulk loads of static data and mark that specific filegroup as read only:
ALTER DATABASE my_db
MODIFY FILEGROUP fg_static_C READ_ONLY
You don't have the maintenance window right now to do perform a differentail or full file backup, and decide to do a transaction log backup. At this point, you will want to switch to full recovery before your next transaction log backup, take the transaction log backup and then switch back to bulk_logged. Otherwise, your bulk logged extents will keep getting backed up for your read only filegroups.
ALTER DATABASE my_db
SET RECOVERY FULL
go
BACKUP LOG my_db
TO DISK='c:\my_db_log.bak' with init
go
ALTER DATABASE my_db
SET RECOVERY BULK_LOGGED
go
Your database continues to be used and data is inserted/modified in the read/write filegroups. You then perform a partial backup of just your read/write filegroups:
BACKUP DATABASE my_db
READ_WRITE_FILEGROUPS
TO DISK='C:\my_db_read_write.bak' with init
More data modification activity occurs on the database and then unfortunately you have drive failure that results in one of your read/write filegroups to disappear. SQL Server was restarted and queries on the database fail because the file is missing.
To simulate this, stop SQL, move file f_read_write_A somewhere else and restart SQL. Try quering the database, it should fail because of the missing file.
What do you do next if you want to just restore the read/write filegroup in question and get your database back online as quick as possible?
-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.
BACKUP LOG my_db
TO DISK='c:\my_db_log_tail.bak' wth init
with NO_TRUNCATE
-- #2 then restore just the damaged file(s)/filegroup from the *partial* backup you took earlier
RESTORE DATABASE my_db FILE='f_readwrite_A' FROM DISK='C:\my_db_read_write.bak'
WITH NORECOVERY
-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.
RESTORE LOG my_db
from DISK='c:\my_db_log.bak'
with norecovery
go
RESTORE LOG my_db
from DISK='c:\my_db_log_tail.bak'
with recovery
Your database is now back online.
However, what if you lost one of your read only filegroups instead? For example, FILEGROUP fg_static_C. In this scenario, keep in mind that you've not yet performed a differentail or full backup since populating that filegroup and marking it read only. What steps would be required to restore that filegroup?
-- #1 back up the tail of the log with no_truncate. You *must* do this before you can do anything else.
BACKUP LOG my_db
TO DISK='c:\my_db_log_tail.bak'
with NO_TRUNCATE, init
-- #2 Start your restore sequence from the last full backup. In this scenario, you must go back to the full backup because the partial only captured the readwrite filegroups.
RESTORE DATABASE my_db FILE='f_static_C' FROM DISK='C:\my_db_full.bak'
WITH NORECOVERY
-- #3 restore all transaction log backup sequences ending with the latest tail you backed up.
RESTORE LOG my_db
from DISK='c:\my_db_log.bak'
with norecovery
go
RESTORE LOG my_db
from DISK='c:\my_db_log_tail.bak'
with recovery
Your database is now back online and ready to go!