Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We have full backups and log backups configured.
During restore we apply full backup and subsequent log backups.
I know that if I take a manual full backup (non copy only mode) then it will break the differential chain. But in this example there is no differential backup. However there is transactional log chain. Will this chain be negatively affected due to the non copy only full backup?
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.
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)
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.
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.
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.
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
A full (or differential) backup do not affect or break the log backup sequence. This is regardless of whether you specify COPY_ONLY or not. correct?
A full (or differential) backup do not affect or break the log backup sequence. This is regardless of whether you specify COPY_ONLY or not.
It will influence the restore GUI and what backup it choses as a base for the restore sequence, though. But that is only a GUI thing.
If you do a backup without copy_only, the restore GUI will start the restore sequence from that backup (until you do the next full backup).
If you do a backup without copy_only, then that will not be the basis for the restore sequence.
