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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

We should get two filegroup for Adventureworks2012.

Now, take backup of “Primary” filegroup with following command.

[sourcecode language=”sql”]
BACKUP DATABASE AdventureWorks2012
FILEGROUP = ‘Primary’
TO DISK = ‘E:\Temp_Testing\Adv2012P.bak’;
GO
[/sourcecode]

Inserting one record in newly created table.

[sourcecode language=”sql”]
INSERT INTO [dbo].[EmpOnNewFileGroup]
SELECT ‘Ritesh Shah’
GO
[/sourcecode]

Now, we are going to take backup of new filegroup we have created along with log backup.

[sourcecode language=”sql”]
–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
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

Mirror backup to take full backup at multiple location in SQL Server

Mirror backup to take full backup at multiple location in SQL Server

Sometime back, one company environmental invited me to investigate some performance related issues there were facing. While investigating issues, I seen their backup strategy. They were taking full backup everyday as they were having database near to 150GB and after taking full backup, they were transferring full backup to external drive as well as in network path by using xcopy command in batch file. This incident forced me to write this article.

Recently we have talked about splitting full database backup in multiple file in one of my previous article. Today I want to show one more useful command “Mirror” for database full backup which helps DBA to copy database full backup file to different location.

Generally we tend to take full backup at regular interval and copy it somewhere else, may be in network location or may be in external drive attached to server. If you are copying backup manually to another location, “Mirror” command is helpful to you, if you have SQL Server 2005 or later version.

All you need to manage is permission. SQL Server service account/login suppose to have proper permission on the drive/network path to write something there.

I am talking full database backup of my AdventureWorks2012 database into local “E” drive as well as at network path. My SQL Server service account has full permission for write in my network path.

[sourcecode language=”sql”]

USE [master]

GO

BACKUP DATABASE AdventureWorks2012

TO DISK = ‘E:\Temp_Testing\AdventureWorks2012.bak’

MIRROR TO DISK = ‘\\WinServ2008R2\DBBackupTesting\AdventureWorks2012.bak’

WITH FORMAT

GO

[/sourcecode]

After executing the above command, I come across following message:

MIrror

Keep updating yourself with technology enhancement will always makes you more productive and efficient.

Knowledge is power!!!!

some more reading about backup & restore:

Find database backup history in SQL Server (Click Here)
Find database restore date in SQL Server (Click Here)
Backup SQL Server database with encrypted data column/table and restore it somewhere else (Click Here)
Split SQL Server Full backup in multiple files for faster backup (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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

Split SQL Server Full backup in multiple files for faster backup

Split SQL Server Full backup in multiple files for faster backup

I had a situation yesterday to download one full database backup from the data-center to our local network. I have taken compressed backup of our database which was around 90 GB and download 90 GB over internet is still a big thing. I had no other choice but to download only.

We have our own FTP between our data-center and our office and I had to use it only. There is a chance that after downloading few GBs If I get a single packet loss in internet, I would have to start from zero all over again. I was not in situation to install DAP or this kind of software which provides “resume” service if downloading gets stopped due to any reason.

So, I have decided to split backup files in 500MB chunk so at any give time, If I lost internet connection, I loss maximum 500MB data and I can afford to start it again. Think If I start downloading of 90 GB and internet lost for few seconds after I completed downloading of 85GB, I would not be in position to spend that much time again.

Splitting backup in multiple files is not only convenient for storing and transferring but it is faster as well. Let us see how we can take SQL Server backup in multiple files.

Herewith, I am going to take full backup of Adventureworks2012 database in one .BAK file only.

[sourcecode language=”sql”]
SET STATISTICS TIME ON
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘E:\SQL2012DataFiles\Adventureworks2012.bak’
SET STATISTICS TIME OFF
GO
[/sourcecode]

Let us see how much time SQL Server has taken to get this backup in one file.

SingleBackup

Now let us take backup in multiple files:

[sourcecode language=”sql”]
SET STATISTICS TIME ON

BACKUP DATABASE AdventureWorks2012
TO
DISK = ‘E:\SQL2012DataFiles\Adventureworks2012_1.bak’
,DISK = ‘E:\SQL2012DataFiles\Adventureworks2012_2.bak’
,DISK = ‘E:\SQL2012DataFiles\Adventureworks2012_3.bak’

SET STATISTICS TIME OFF
GO
[/sourcecode]

I have taken backup of Adventureworks2012 database in three files, here is the statistics.

MultipleBackup

You can see backup in multiple file is faster then previous attempt. However, you can’t see major difference as this database is small, you can see major difference if you are having database with few GBs.

Now, it would be interesting to see, how we can restore database from multiple files. Here is the script for the same:

[sourcecode language=”sql”]
USE [master]
GO

RESTORE DATABASE [AdventureWorks2012]
FROM
DISK = N’E:\SQL2012DataFiles\AdventureWorks2012_1.bak’
, DISK = N’E:\SQL2012DataFiles\AdventureWorks2012_2.bak’
, DISK = N’E:\SQL2012DataFiles\AdventureWorks2012_3.bak’
WITH FILE = 2, NOUNLOAD, STATS = 5
GO
[/sourcecode]

some more reading about backup & restore:

Find database backup history in SQL Server (Click Here)
Find database restore date in SQL Server (Click Here)
Backup SQL Server database with encrypted data column/table and restore it somewhere else (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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