Backup and Restore Filegroup in SQL Server

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:

Filegroup

So, this is how we can restore filegroup.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

One thought on “Backup and Restore Filegroup in SQL Server

Comments are closed.