Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 330

3. Let us take down the PieceMealDB database that will simulate OFFLINE action

as follows:
Use master
go
ALTER DATABASE [PieceMealDB] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

4. Also, there is another way to simulate the database corruption: shut down the SQL

Server service and simulate a lost disk subsystem by deleting the primary file.
Shut down SQL Server
Delete the secondary datafile from PMDB_Secondary.ndf the C:\
Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2U\
MSSQL\DATA\ folder for PieceMealDB database
Restart the SQL Server service
After restarting, the PieceMealDB database will not start up and the error
log will represent the following error message:

Error: 17207, Severity: 16, State: 1.
FCB::Open: Operating system error 2(error not found)
occurred while creating or opening file C:\Program Files\
Microsoft SQL Server\MSSQL10_50.SQL2K8R2U\MSSQL\DATA\.
Diagnose and correct the operating system error, and retry
the operation.

5. Now, we will perform a database RESTORE , recovering only PRIMARY filegroup and

LOG file.

6. Initially, we will restore the database from a full database backup using the

NORECOVERY option in order to allow the corresponding transaction log restore task:
RESTORE DATABASE [PieceMealDB]
FILEGROUP = 'PRIMARY'
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\Backup\PieceMealDB.bak'
WITH PARTIAL, NORECOVERY, REPLACE;
GO

7. Once the successful restore of the full database is completed, restore the transaction

log using the following statement:
RESTORE LOG [PieceMealDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\Backup\PieceMeal_TlogAfterBackup.bak'
WITH RECOVERY;
GO