How to: Partial Restore After Media/Drive Failure

sarahhen — Mon, 23 Jul 2007 13:06:00 GMT

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

ALTER DATABASE my_db

SET RECOVERY bulk_logged

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;

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

BACKUP LOG my_db

TO DISK='c:\my_db_log.bak' with init

ALTER DATABASE my_db

SET RECOVERY BULK_LOGGED

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

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

RESTORE LOG my_db

from DISK='c:\my_db_log_tail.bak'

with recovery

Your database is now back online and ready to go!

Search results matching tags 'Disaster Recovery' and 'filegroups'

How to: Partial Restore After Media/Drive Failure