2 Restore database (4) Piece meal Restore–Online restore

Piecemeal restore refers to restore just page,file or file group. If the SQL Server is Enterprise. SQL Server may do the online restore which means the database will be online and only the restoring file or file group is not accessible.

Lets Create new file group and add 1 file to the file group

1. -- Create 2nd file group
2. USE [master]
3. GO
4. ALTER DATABASE [AdventureWorks_new] ADD FILEGROUP [MYFILEGOUP]
5. GO
6. -- Add 2 files on the file group
7. USE [master]
8. GO
9. ALTER DATABASE [AdventureWorks_new] ADD FILE ( NAME = N'GROUP2file1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\GROUP2file1.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [MYFILEGOUP]
10. GO

Exam the file group status

1. --- Exam FIlegroup
2. use [AdventureWorks_new]
3. go
4. SELECT df.name, df.size*8/1024 AS [SIZE IN MB],fg.name
5. FROM sys.database_files df
6. INNER JOIN
7. sys.filegroups fg on df.data_space_id = fg.data_space_id

Create the Table on the new file group

1. -- Create Tabe1 on FIle 1 add 1000 row data
2. use [AdventureWorks_new]
3. go
4. CREATE TABLE TB_1
5.          (number INT IDENTITY(1,1) NOT NULL,
6.           name varchar(50) NOT NULL,
7.           updateTIme datetime default getdate(),       
8.          ) on MYFILEGOUP

Backup the database

1. --- Backup database
2. BACKUP DATABASE  [AdventureWorks_new] to DISK ='C:\dump\AdventureWorks_new.1.db' with format
3. --- Exam the backup file
4. RESTORE FILELISTONLY FROM DISK ='C:\dump\AdventureWorks_new.1.db'

Insert some data

1. --- Insert Test data
2. use [AdventureWorks_new]
3. go
4. DECLARE @count INT
5. SET @count = 0
6. WHILE (@count < 40000)
7. BEGIN
8.    Insert into TB_1 (name)values('SUMMER')
9.    SET @count = (@count + 1)
10. END

Backup the transaction log

1. -- Backup Log
2. BACKUP LOG [AdventureWorks_new] to DISK ='C:\dump\tran\AdventureWorks_new.1.log' with format

Exam the file group size change

1. --- Exam FIlegroup
2. use [AdventureWorks_new]
3. go
4. SELECT df.name, df.size*8/1024 AS [SIZE IN MB],fg.name
5. FROM sys.database_files df
6. INNER JOIN
7. sys.filegroups fg on df.data_space_id = fg.data_space_id
8. GO
9. --- Exam table
10. use [AdventureWorks_new]
11. go
12. declare @id int
13. set @id = object_id('TB_1')
14. exec sp_objectfilegroup @id
15. go
16. sp_spaceused TB_1
17. go
18. select top 5 * from TB_1
19. go

To simulate the file group corruption, we need to shutdown the database and delete the  GROUP2file1.ndf then restart the SQL Server.

Upon the SQL Server start up, you will find out the Database is in the RECOVERY PENDING state, the error log will also indicate it can not find the database file.

1. --- database status
2. SELECT state_desc DatabaseStatus_sysDatabase
3. FROM sys.databases
4. WHERE name = 'AdventureWorks_new'
5. GO

Lets start the piece meal recovery

First, we need to backup the tail log

1. --- Backup tail log
2. use master;
3. BACKUP LOG  [AdventureWorks_new] TO DISK ='C:\dump\tran\AdventureWorks_new.tail.log' WITH NORECOVERY, NO_TRUNCATE, FORMAT

Check the Database status again, the status become “RESTORING”

1. --- database status
2. SELECT state_desc DatabaseStatus_sysDatabase
3. FROM sys.databases
4. WHERE name = 'AdventureWorks_new'
5. GO

Since the Primary file group is intact, Lets bring it up ASAP

Restore Primary file group ,  log 1 and the tail log

1. --- Restore filgrop from backup
2. RESTORE DATABASE  [AdventureWorks_new] FILEGROUP='PRIMARY' FROM disk ='C:\dump\AdventureWorks_new.1.db'
3. WITH PARTIAL, NORECOVERY
4. --- Revcover log
5. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.1.log'  WITH NORECOVERY
6. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.tail.log'  WITH RECOVERY

Check the status again, the database is online now, but if you try to select the TB1, you will get the error message regarding the file group is not online. The table in the primary file group will be accessible for user .

Lets restore the 2nd file group.

1. --- Recover the filgroup
2. use master;
3. RESTORE DATABASE  [AdventureWorks_new] FILEGROUP='MYFILEGOUP' FROM disk ='C:\dump\AdventureWorks_new.1.db' WITH NORECOVERY
4. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.1.log'  WITH NORECOVERY
5. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.tail.log'  WITH RECOVERY

Now , you have fully usable database.

Reference

Performing Piecemeal Restores http://msdn.microsoft.com/en-us/library/ms177425.aspx

Perform Page restore  http://msdn.microsoft.com/en-us/library/ms175168.aspx