Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Error messages:
Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)
Get help: http://help/544

Solution : You will get this type of error when you try to insert identity column in replication. You need to update column with not for replication.

This option sets identity off for the records which are coming throw replication.

Use SubscriberDBName
Go
Alter Table TableName
Alter Column ColumnName Not For Replication -- Your Identity column.

Backup and Restore FileGroup

Step1. Create a db with two file groups

CREATE DATABASE [FGBackup]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'FGBackup', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N'FGBackup_Sec', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Sec.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FGB]
( NAME = N'FGBackup_FGB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'FGBackup_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Step2. Create two tables, one on each FG

USE [FGBackup]
GO
Create Table FGTesting_Primary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Primary]

Create Table FGTesting_Secondary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Secondary]

Create Table FGTesting_FGB
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [FGB]

Step3. Insert a row into each table

Insert into FGTesting_Primary values('TestingBefore1',GetDate())
Insert into FGTesting_Primary values('TestingBefore2',GetDate())
Insert into FGTesting_Primary values('TestingBefore3',GetDate())
Insert into FGTesting_Secondary values('TestingBefore1',GetDate())
Insert into FGTesting_Secondary values('TestingBefore2',GetDate())
Insert into FGTesting_Secondary values('TestingBefore3',GetDate())
Insert into FGTesting_FGB values('TestingBefore1',GetDate())
Insert into FGTesting_FGB values('TestingBefore2',GetDate())
Insert into FGTesting_FGB values('TestingBefore3',GetDate())

Select * From FGTesting_Primary
Select * From FGTesting_Secondary
Select * From FGTesting_FGB

Step4. Backup FG 1

Backup Database FGBackup
FileGroup = 'Primary'
To Disk = 'D:\FG\Priamry.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'Secondary'
To Disk = 'D:\FG\Secondary.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'FGB'
To Disk = 'D:\FG\FGB.bck'

Step5. Restore as a new DB

RESTORE DATABASE [FGBackup_Primary]
FILE = N'FGBackup'
FROM DISK = N'D:\FG\Priamry.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary_1.ldf',
NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [FGBackup_Secondary]
FILE = N'FGBackup',
FILE = N'FGBackup_Sec'
FROM DISK = N'D:\FG\Secondary.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary1.mdf',
MOVE N'FGBackup_Sec' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_1.ldf',
NOUNLOAD, STATS = 10
GO

RESTORE DATABASE [FGBackup_FGB]
FILE = N'FGBackup',
FILE = N'FGBackup_FGB'
FROM DISK = N'D:\FG\FGB.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary2.mdf',
MOVE N'FGBackup_FGB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_2.ldf',
NOUNLOAD, STATS = 10
GO

Step6. One table should be accessible. Other should be down

use [FGBackup_Primary]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB Step Not available

use [FGBackup_Secondary]
select * from FGTesting_Primary
select * from FGTesting_Secondary
select * from FGTesting_FGB Step Not available
use [FGBackup_FGB]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB