Troubleshoot SQL Server Error for backups–: Msg 3136, Level 16

Is it possible to use COPY_ONLY backup for restoring differential backups? No, if you try to do so, SQL Server raises error Msg 3136, Level 16.

Let’s simulate the issues. The following script does the following tasks.

• Take a full backup of the SQL database with COPY_ONLY

• Take a differential backup

BACKUP DATABASE DBBlogger TO DISK = 'DBBlogger_cp.bak' 
WITH COPY_ONLY, FORMAT
GO
BACKUP DATABASE DBBlogger TO DISK = 'DBBlogger_01.bak' 
WITH DIFFERENTIAL, FORMAT
GO

Now, let’s try to restore the SQL Database backup. The differential backup requires a full backup to restore before restoring it.

Restore full backup(COPY_ONLY) in NORECOVERY MODE.

USE [master]
RESTORE DATABASE [DBBlogger_1] FROM 
DISK = N'C:\SQL\DBBlogger_cp.bak'
WITH  FILE = 1, 
MOVE N'DBBogger' TO N'C:\SQL\DBBogger.mdf', 
MOVE N'DBBogger_log' TO N'C:\SQL\DBBogger_log.ldf', 
NORECOVERY,NOUNLOAD,  STATS = 5

Restore the differential backup on top of the full backup(COPY_ONLY).

RESTORE DATABASE [DBBlogger_1] 
FROM  DISK = N'C:\SQL\DBBlogger_01.bak'
WITH RECOVERY,  NOUNLOAD, STATS = 10
GO

You need to use the full backup taken without any COPY_ONLY keyword to resolve the error. You can run the following script if you want to identify a COPY_ONLY backup.

SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name
                FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 1