1

I am running encrypted backup on SQL Server 2017.

Doing a test restore, it failed:

RESTORE DATABASE [test] 
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\FULL\DPH-SQL-SLI-12P_BTBLEAD_FULL_20201021_185145.bak' 
WITH NORECOVERY, REPLACE; 

RESTORE DATABASE [test] 
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\DIFF\DPH-SQL-SLI-12P_BTBLEAD_DIFF_20201022_071002.bak' 
WITH NORECOVERY; 
Processed 199264 pages for database 'test', file 'BTBLEAD_Data' on file 1.
Processed 2 pages for database 'test', file 'BTBLEAD_Log' on file 1.
RESTORE DATABASE successfully processed 199266 pages in 446.348 seconds (3.487 MB/sec).
Msg 3136, Level 16, State 1, Line 6 This differential backup cannot be restored
because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 6 RESTORE DATABASE is terminating abnormally.

It WAS running flawlessly, until they installed Commvault cloud backup.

I found that if a Commvault backup kicks off, I can no longer restore my native local backup past the point in time that Commvault kicked off!

Apparently, SQL Server resets the LSN on each backup, but doesn't know there are two distinct programs running. When Commvault kicks off, the LSN is brought forward, and my native backup does not know about it

I like having my own local backup, as it is faster and quicker to restore than the cloud backup, than waiting for another group to do the restore from the cloud for me.

Is there any way to keep the backups separated?

Another group manages the Commvault backup. I am new to SQL Server - I am an Oracle DBA by trade.

4

Differential backups are attached to the Full. If your other backup tool takes a full, it's normal that you cannot restore your diff without that full.

You should not use 2 different backup tool in parallel. If you really need to, then you can check if it's possible to specify the "copy_only" option for the backup in CommVault (so your native backup will remains the "official" backups).

If it's not possible, you could then do only full (with copy only) on your side. Of course, if the database are big, this mean bigger backup and longer time to restore.

Maybe you should address the problem at the source. It seems to me that what you really need is permission in CommVault to be able to run restore from that tool.

  • Thank you. That is how I understood it. Another group manages the commvault backup. I have not looked at copy_only backups. (I am am new to sqlserver, I am an Oracle DBA by trade) . Is that like an oracle Image copy backup ? – Paul Seeberg Oct 23 '20 at 16:12
2

Is there any way to keep the backups separated?

Assuming that CommVault is also taking log backups, and can't be configured to take COPY_ONLY backups, you will only be able to restore FULL backups, and not apply differentials or log backups.

I am an Oracle DBA by trade) . Is that like an oracle Image copy backup ?

No. Normally a FULL backup will write the LSN at which the backup completes back into the database, and any subsequent DIFFERENTIAL backup will only contain pages updated after that LSN. This is called "reseting the differential base". And a COPY_ONLY full backup just skips this step.

  • 1
    Thanks for the clarification. It is was I assumed. Was hopeful there was a workaround :) I guess my best bet in this situation is to take regular copy_only backups and let the Commvault backup do it's thing. – Paul Seeberg Oct 26 '20 at 14:09
0

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:

  1. Talking to the people responsible for the company-wide backup policy to ensure you are complying with the RPO and RTO of the company.
  2. 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.
  3. 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

  • Thank you. That is very helpful. Our compromise, was actually to leave the commvault as is, and doing a couple copy only backups during the day as an emergency backup in case commvault was not working for whatever reason. – Paul Seeberg Dec 16 '20 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.