All ad-hoc backup’s requests should be treated important as this can harm your recovery plan during emergency.Starting SQL 2005 we have an option to create COPY_ONLY backup which will not alter the differential_log_sequence(The differential_base_lsn affects the sequence in which combination of backups are to be restored in a recovery).

This concept is proved and tested using the below scripts.

–Backup the database AdventureWorksDW

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’

–Check the differential_base_lsn

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000011300037*/

–Prove that normal full backup will change the differential_base_lsn

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’

GO

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000013800037 */
–Now take a Copy-Only backup and we can note that LSN is not changed.

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’
WITH COPY_ONLY

GO

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000013800037 */