If we just take your question and analyse the information stored in the msdb database, then the answer is: Yes, a BACKUP DATABASE ...
with the COPY_ONLY
parameter not set, will have an impact on the backup chain (history...).
This is because the database_backup_lsn
(LSN = Log Sequence Number) which is stored together with the backup information is changed once a FULL BACKUP DATABASE...
statement is executed.
Subsequent Transaction Log (TLOG) backups reference the FULL backup. This is displayed in the following picture:

The Log backup created at 2021-09-11 22:19:17.000 references the database_backup_lsn
with the LSN 1221000000228000037
which is the first_lsn
of the last FULL backup which occurred about a week ago.
The Log backup created at 2021-09-11 23:15:13.000 references the database_backup_lsn
with the LSN 1221000000714400037
, which is the first_lsn
of the FULL database backup that occurred at 2021-09-11 22:34:32.000.
How does this impact your restore?
Well if you have an unbroken chain of TLOG backups available since a given FULL backup, then there is no impact whatsoever. You can restore your database with a FULL database backup and subsequent TLOG backup files up to any point in time.
However, if you have a broken chain of TLOG backups, then you would only be able to restore the database with a FULL backup and any available TLOG backups up until the missing TLOG files.
How to tell if a backup log chain is broken? (DBA Stackexhange answer)
Information
A FULL backup contains the consistent state of your database.
(Caveat: You have to restore this file to prove that. Or a backup is only valid after it has been restored.)
A TLOG backup contains all the committed transactions since your last backup (FULL or TLOG). The backup contains a first_lsn
and a last_lsn
. As these are normally in a sequence, because the last_lsn
of the previous TLOG backup is the first_lsn
of the next TLOG backup, then you should have a consecutive sequence of TLOG backups that will allow you to restore the database to any point in time, provided you have an unbroken chain of TLOG backups.
COPY_ONLY Parameter
The COPY_ONLY
parameter just tells SQL Server to perform a FULL backup, but to not reset the database_backup_lsn
stored in the msdb backup history. All subsequent TLOG backups will be still based on the last FULL backup which was created without the COPY_ONLY
parameter.
The SSMS GUI
Performing a FULL backup without the COPY_ONLY
flag will change how the information is displayed in the GUI. The GUI normally display the last FULL, DIFF and TLOG backups required to bring the database back into a consistent state based on the time specified. This is because the GUI reads the msdb backup history information.
Script to Query msdb Backup History
I've added the script I use for your convenience.
/* ==================================================================
Author......: hot2use
Date........: 25.04.2018
Version.....: 0.1
Server......: localhost (first created for)
Database....: msdb
Owner.......: -
Table.......: various
Type........: Script
Name........: ADMIN_Retrieve_Backup_History_Information.sql
Description.: Retrieve backup history information from msdb database
............
............
............
History.....: 0.1 h2u First created
............
............
================================================================== */
SELECT /* Columns for retrieving information */
-- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
-- msdb.dbo.backupset.expiration_date,
CASE msdb.dbo.backupset.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END AS backup_type,
-- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
-- msdb.dbo.backupset.name AS backupset_name,
-- msdb.dbo.backupset.description,
msdb.dbo.backupset.is_copy_only,
msdb.dbo.backupset.is_snapshot,
msdb.dbo.backupset.checkpoint_lsn,
msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.differential_base_lsn,
msdb.dbo.backupset.first_lsn,
msdb.dbo.backupset.fork_point_lsn,
msdb.dbo.backupset.last_lsn
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
/* ----------------------------------------------------------------------------
Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1
/* ----------------------------------------------------------------------------
WHERE statement to find Device Backups with '{' and date n days back
------------------------------------------------------------------------------- */
-- AND physical_device_name LIKE '{%'
/* -------------------------------------------------------------------------------
WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b
---------------------------------------------------------------------------------- */
-- AND physical_device_name LIKE '[fF]:%' -- STANDARD F: Backup Directory
-- AND physical_device_name NOT LIKE '[nN]:%' -- STANDARD N: Backup Directory
-- AND physical_device_name NOT LIKE '{%' -- Outstanding Analysis
-- AND physical_device_name NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
-- AND backupset_name NOT LIKE '%Galaxy%' -- CommVault Sympana Backup
/* -------------------------------------------------------------------------------
WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b
----------------------------------------------------------------------------------
AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) -- 7 days old or younger
AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE()) -- n days old or older
*/
/* -------------------------------------------------------------------------------
WHERE Statement to find backup information for (a) given database(s)
---------------------------------------------------------------------------------- */
-- AND database_name IN ('AdventureWorks2012') -- database names
-- AND database_name IN ('rtc') -- database names
/* -------------------------------------------------------------------------------
ORDER Clause for other statements
---------------------------------------------------------------------------------- */
--ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause
---WHERE msdb..backupset.type = 'I' OR msdb..backupset.type = 'D'
ORDER BY
--2,
2 DESC,
3 DESC