As an alternative option you could use the FULL backup of the database and all its Transaction Log backups up until the point required.
I discussed this in my answer to the question How to tell if a backup log chain is broken? (DBA.SE).
The thing is that any backup that is created on the SQL Server instance stores some backup history information in the msdb database in the tables msdb.dbo.backupmediafamily and msdb.dbo.backupset.
Additionally, performing a FULL backup resets the base LSN (log sequence number) for any future DIFF (differential) backups. This means that a DIFF backup is always based on the last FULL backup.
Your Situation
If you run my query from my post (above):
/* ==================================================================
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
...you will retrieve a list of backups that were performed against your database(s). Change the WHERE clause AND database_name IN ('AdventureWorks2012') -- database names to reflect your database.
You will see that the backups have different differential_base_lsn for the DIFF backups of your database.
As (previously) mentioned (by others) you can circumvent this issue by:
- Talking to the people responsible for the company-wide backup policy to ensure you are complying with the RPO and RTO of the company.
- Change either your backup to perform
COPY_ONLY backups or ask that the centralised Comvvault backups be modified to perform COPY_ONLY backups, AFTER talking with the people responsible for company-wide backups.
- Instead of using a
FULL and a DIFF backup, use your FULL and all TLOG backups up until your restore point-in-time.
Answering Your Questions
Is there any way to keep the backups separated?
No, because your backup script and Commvault's backup are both performing native SQL Server backups using the BACKUP DATABASE ... command. SQL Server will then store the information in the msdb tables mentioned.
The msdb database is similar to Oracle's control file and/or the central RMAN Catalog.
COPY_ONLY and Oracle equivalent
I don't think that Oracle has an exact equivalent to SQL Server's COPY_ONLY parameter for backups as the inner working for Oracle's RMAN and SQL Server's BACKUP DATABASE ... are based on different architectures.
Image Copies in Oracle might be similar to the DBCC CLONEDATABASE (Transact-SQL) command in SQL Server or an OFFLINE copy of the *.mdf and *.ldf database files of a SQL Server database.
I think the only similarity with Oracle's SQN# and Microsoft's LSN when having restore issues, would be the case when incarnations are involved which I discussed in my answer for What is an orphan incarnation? (DBA.SE).
However, this is very loosely related...
In Oracle you can restore/recover a database to any point-in-time if you have the correct INC 0, INC 1 and Archive Log backups from the same incarnation.
But even in Oracle having INC 0, INC 1 and Archive Log backups being monitored in the control file and/or central RMAN catalog, you could have issues with various backup solution running at different times which are based on the native Oracle RMAN.
Think Commvault and a local cronjob
