0

As per books online Differential backups are not supported on secondary replicas. Also as per this link COPY_ONLY has no effect when specified with the DIFFERENTIAL option. I am using SQL2014 SP1. I can take differential backup with adding 'copy_only' option and I understand that does not have any effect but I am able to take differential in secondary replica. Which contradicts books online. I can use that differential backup with the full backup taken at primary replica. Why it is allowing me to do this? Full code for reproducing the scenario I am explaining.

--Connect to primary replica
USE [AGTest]
GO
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
    DROP TABLE dbo.t1;
GO
CREATE TABLE t1 (
    c1 INT
    ,c2 VARCHAR(10)
    );
GO
DECLARE @counter INT = 1

WHILE (@counter < 21)
BEGIN
    INSERT INTO t1
    VALUES (
        @counter
        ,REPLicate('a', 10)
        );

    SELECT @counter = @counter + 1;
END
GO
--20 rows are inserted
SELECT count(0)
FROM dbo.t1;

--Take a full backup on primary
BACKUP DATABASE [AGTest] TO DISK = N'J:\AG_Test_Full.bak'
WITH NOFORMAT
    ,NOINIT
    ,NAME = N'AGTest-Full Database Backup on primary'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10

--adding another 20 rows
USE [AGTest]
GO

DECLARE @counter INT = 1

WHILE (@counter < 21)
BEGIN
    INSERT INTO t1
    VALUES (
        @counter
        ,REPLicate('a', 10)
        );

    SELECT @counter = @counter + 1;
END
GO

--Taking a diff backup with 'copy_only'
--Connected to preffered secondary
IF sys.fn_hadr_backup_is_preferred_replica('AGTest') = 1
BEGIN
    PRINT 'taking backup here'

    BACKUP DATABASE [AGTest] TO DISK = N'J:\AG_Test_diff.bak'
    WITH differential
        ,copy_only
        ,NOFORMAT
        ,NOINIT
        ,NAME = N'AGTest-diff Backup on Secondary'
        ,SKIP
        ,NOREWIND
        ,NOUNLOAD
        ,STATS = 10
END
ELSE
BEGIN
    PRINT 'I am not prefered backup node'
END
GO

--Restore with different name AGTest2 using using full backup I took on primary
--You can use any replica node for this.
USE [master]
GO

RESTORE DATABASE [AGTest2]
FROM DISK = N'J:\AG_Test_Full.bak'
WITH FILE = 1
    ,MOVE N'AGTest' TO N'L:\AGTest2.mdf'
    ,MOVE N'AGTest_log' TO N'F:\AGTest2_log.ldf'
    ,STANDBY = N'J:\AGTest_RollbackUndo_2016-10-18_07-53-01.bak'
    ,NOUNLOAD
    ,STATS = 5
GO

USE [agtest2]
GO
--should give a count of 20
SELECT count(0)
FROM dbo.t1;

--Restoring the diff I took on secondary with copy_only option
USE [master]
GO

RESTORE DATABASE [AGTest2]
FROM DISK = N'J:\AG_Test_diff.bak'
WITH FILE = 1
    ,MOVE N'AGTest' TO N'L:\AGTest2.mdf'
    ,MOVE N'AGTest_log' TO N'F:\AGTest2_log.ldf'
    ,STANDBY = N'J:\AGTest_RollbackUndo_2016-10-18_07-53-01.bak'
    ,NOUNLOAD
    ,STATS = 5
GO


USE [agtest2]
GO
--should give a count of 40
SELECT count(0)
FROM dbo.t1;
  • If your Backup Preferences is set to Primary then you will be able to create differential backups. Check that option in your configuration. See msdn.microsoft.com/en-us/library/hh710053.aspx – Kin Shah Oct 18 '16 at 14:35
  • Please script out the AG config. – Nic Oct 18 '16 at 14:40
  • Kin I can take differential backup, my question was why I am allowed to do it in secondary. – SqlWorldWide Oct 18 '16 at 15:06
  • Nic for some reason I cannot add any more code with AG configuration – SqlWorldWide Oct 18 '16 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.