Backup and Restore Filegroup in SQL Server
Backup and Restore of database is one of the primary task of any DBA. Taking up full backup of database at regular interval and restore it whenever and wherever it is needed is very popular but it may not be possible for every database. Some production databases are too big and we can’t take full backup of database frequently and in this scenario, file / filegroup backup comes to rescue.
If you are operating database with size of few thousand GBs, It becomes impractical to take full database backup and restore full database backup as it might take few hours to complete this operation. Fortunately SQL Server 2005 and later Edition provides an answer to this challenge through piecemeal restore.
I will be using AdventureWorks2012 database with FULL recovery mode in my testing server. Adventureworks2012 database comes up with one default filegroup which is “Primary” but I will add one more File with new filegroup to demonstrate this exercise.
USE [master] GO --Add New Filegroup ALTER DATABASE [AdventureWorks2012] ADD FILEGROUP [NewFileGroup] GO USE [master] GO --Add new data file with .NDF extension ALTER DATABASE [AdventureWorks2012] ADD FILE ( NAME = N'AdvNewFile', FILENAME = N'E:\SQL2012DataFiles\AdvNewFile.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [NewFileGroup] GO USE [AdventureWorks2012] GO --create new table in newly created filegroup CREATE TABLE [dbo].[EmpOnNewFileGroup]( [ID] [int] IDENTITY(1,1) NOT NULL, [empName] [varchar](100) NULL ) ON NewFileGroup GO
Now, let us confirm the total number of database files / filegroup along with its path and size with simple TSQL script which was given in my earlier article too.
USE [AdventureWorks2012] GO SELECT dbfile.name AS DatabaseFileName, dbfile.size/128 AS FileSizeInMB, sysFG.name AS FileGroupName, dbfile.physical_name AS DatabaseFilePath FROM sys.database_files AS dbfile INNER JOIN sys.filegroups AS sysFG ON dbfile.data_space_id = sysFG.data_space_id GO
We should get two filegroup for Adventureworks2012.
Now, take backup of “Primary” filegroup with following command.
BACKUP DATABASE AdventureWorks2012 FILEGROUP = 'Primary' TO DISK = 'E:\Temp_Testing\Adv2012P.bak'; GO
Inserting one record in newly created table.
INSERT INTO [dbo].[EmpOnNewFileGroup] SELECT 'Ritesh Shah' GO
Now, we are going to take backup of new filegroup we have created along with log backup.
--Back up the files in newFileGroup we have just created. BACKUP DATABASE AdventureWorks2012 FILEGROUP = 'NewFileGroup' TO DISK = 'E:\Temp_Testing\adv2012N1.bak'; GO BACKUP LOG AdventureWorks2012 TO DISK ='E:\Temp_testing\adv2012Log.bak' GO
We have crossed 50% benchmark now and we are on the half way. We are done with backup stuff but we have to make sure that we are able to restore backed up database whenever we want so I will be creating new database “Adventureworks2012New”, one the same SQL Server instance , by using the backup we have taken. Do remember that I don’t have any full backup at the moment.
USE [master] GO --restoring Primary filegroup backup with PARTIAL and NORECOVERY (offline restore) RESTORE DATABASE [AdventureWorks2012New] FILEGROUP= 'Primary' FROM DISK= 'E:\Temp_Testing\Adv2012P.bak' WITH PARTIAL, NORECOVERY, MOVE N'AdventureWorks2012_Data' TO N'E:\SQL2012DataFiles\Temp\AdventureWorks2012New_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'E:\SQL2012DataFiles\Temp\AdventureWorks2012New_Log.mdf' GO --retoring new filegroup backup with NORECOVERY RESTORE DATABASE [AdventureWorks2012New] FILE = N'AdvNewFile' FROM DISK = N'E:\Temp_Testing\adv2012N1.bak' WITH NORECOVERY, MOVE N'AdvNewFile' TO N'E:\SQL2012DataFiles\Temp\AdvNewFileNew.ndf' GO --LOG restore with recovery RESTORE LOG [AdventureWorks2012New] FROM DISK ='E:\Temp_testing\adv2012Log.bak' WITH RECOVERY
Now, let us check whether one record in the newly created table is restored or not? I am executing SELECT query on that table from both databases, I have following result:
So, this is how we can restore filegroup.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.