none
DPM 2019 RU1 SQL backp job fails RRS feed

  • Question

  • I have checked the recovery models of databases hasn't been changed and that DPMRA has rights to the DB's

    I get the below..

    If I then run the Recovery point manually it works

    Any ideas ?


    Affected area:    Server\ReportServer
    Occurred since:    13/03/2020 11:16:22
    Description:    Recovery point creation jobs for SQL Server 2014 database Server\ReportServer on Server.domain.co.uk have been failing. The number of failed recovery point creation jobs = 2.
     If the data source protected has some dependent data sources (like a SharePoint Farm), then click on the Error Details to view the list of dependent data sources for which recovery point creation failed. (ID 3114)
        Execution of SQL command failed for SQL Server 2014 database Server\ReportServer on Server.domain.co.uk with reason : BACKUP LOG is terminating abnormally.
    BACKUP LOG cannot be performed because there is no current database backup.
    . (ID 30173 Details: Internal error code: 0x80990D18)
        More information
    Recommended action:    Check the Application Event Viewer logs on the SQL Server for entries posted by the SQL Server service to find out why the SQL command may have failed. For more details, look at the SQL Server error logs.
        Create a recovery point...
    Resolution:    To dismiss the alert, click below
        Inactivate

    Friday, March 13, 2020 12:09 PM

All replies

  • Hi,

    What is the recovery model of the SQL Server?

    Similar thread here:
    SQL Backups will only complete if run manually

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, March 13, 2020 12:37 PM
  • Hi Leon,

    Thanks I saw that thread, will check and get back to you. I did ask the question if they had been changed.. ;-)

    Have a nice weekend..

    Cheers,

    LM


    • Edited by Lord Melch Friday, March 13, 2020 12:46 PM
    Friday, March 13, 2020 12:45 PM
  • Any update on your query?

    Blog: https://thesystemcenterblog.com LinkedIn:

    Monday, April 6, 2020 9:02 PM
  • Speaking to the SQL Admins different models on different databases.

    I presume as long as the model type is not changed DPM won't complain ?

    Tuesday, April 7, 2020 8:49 AM
  • When you change recovery models of databases, you should reprotect the databases (remove protection with retain data and re-protect).

    Blog: https://thesystemcenterblog.com LinkedIn:

    Tuesday, April 7, 2020 8:53 AM
  • When you change recovery models of databases, you should reprotect the databases (remove protection with retain data and re-protect).

    Blog: https://thesystemcenterblog.com LinkedIn:

    Apprently the recovery mode hasn't been changed.. I have unprotected and reprotected with no luck.

    Any other ideas ?

    Friday, July 3, 2020 9:18 AM
  • Did you remove the protected SQL databases from the protection group, then change the recovery model to Full then refresh the host server before adding/modifying a protection group with the same SQL databases?

    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, July 3, 2020 9:25 AM
  • Did you remove the protected SQL databases from the protection group, then change the recovery model to Full then refresh the host server before adding/modifying a protection group with the same SQL databases?

    Blog: https://thesystemcenterblog.com LinkedIn:

    No.  I Edited the Protection Group, deselected SERVER - All SQL Shares (Next, Next)

    Then same again but selected All SQL Shares (after waiting some 15-20 mins as it takes ages saying "in progress")

    "then change the recovery model to Full then refresh the host server before adding/modifying a protection group with the same SQL databases?"

    How do I do that, and do all SQL databases have to have a Full recovery model ?



    • Edited by Lord Melch Friday, July 3, 2020 9:35 AM
    Friday, July 3, 2020 9:34 AM
  • SQL logs will not be purged if you take express full backup from DPM, you need to set a synchronisation frequency in the protection group in order to take a log backup. Once log backup is successful, sql will make those T-logs for truncation.

    If you want all of the the transaction logs to be truncated, you'll also need the Recovery Model to be configured to "Full", and for this change to take effect you should remove the SQL workloads from the protection group first, then change the recovery model, wait a while/refresh, then re-add the SQL workloads to the protection group. 


    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, July 3, 2020 9:47 AM
  • SQL logs will not be purged if you take express full backup from DPM, you need to set a synchronisation frequency in the protection group in order to take a log backup. Once log backup is successful, sql will make those T-logs for truncation

    Errr... apologies I don't understand, by Express Full Backup are you talking about the Short-Term Recovery Goals

    Those are set to:

    Retention 5 days

    Sync Evey 15 mins

    I also have a time set in the Application recovery points, to do an Express Full Backup at 20:00 Everyday for the protection group


    • Edited by Lord Melch Friday, July 3, 2020 9:57 AM
    Friday, July 3, 2020 9:56 AM
  • SQL Server databases that are log-shipped, in read-only mode, or that use the simple recovery model do not support incremental backup. Recovery points are created for each express full backup only.

    For all other SQL Server databases, synchronization transfers a transaction log backup, and recovery points are created for each incremental synchronization and express full backup. The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up.


    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, July 3, 2020 10:32 AM
  • That's Greek to me Leon.. :-) thanks for the feedback as ever

    The SQL guys have suggested I set the Retention Range Sych Frequency to every 1hr rather than 15 mins to see if it's conflicting with their house keeping jobs.


    • Edited by Lord Melch Friday, July 3, 2020 11:14 AM
    Friday, July 3, 2020 11:13 AM
  • How often do you perform an express full backup?


    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, July 3, 2020 11:29 AM
  • Currently once a day at 20:00
    Friday, July 3, 2020 11:35 AM
  • The problem seen above "BACKUP LOG cannot be performed because there is no current database backup." eventually stumps junior DBAs sometimes. This scenario is typically seen when the recovery model of a database was changed in production (typically from full to simple to shrink an overgrown log file - then back to full without taking an immediate full backup after changing back to the full recovery model) The switch from full to simple invalidates the prior existing transaction log backup chain. The subsequent switch back to full recovery model requires a full backup to be taken so the following transaction log backups have a point of reference. In DPM this error typically self corrects like it does in SQL server agent jobs - as soon as a full backup has been taken the transaction log backups will work again. 

    Hope this helped 

    Sassan Karai


    Sassan Karai

    Tuesday, July 7, 2020 2:47 AM
  • Sassan,

    Thankyou for the reply..

    >as soon as a full backup has been taken the transaction log backups will work again.

    What do I need to do from a DPM perspective, or are you saying a full SQL backup has to be taken (by the SQL DBA in their SQL tools) before the DPM jobs run ?

    I have the Retention Range Sych Frequency to every 1hr and an Express Full backup set to 20:00 once a day.

    Do the DPM jobs have to be set around whatever the SQL DBA's do ?

    Cheers,

    LM


    Tuesday, July 7, 2020 6:56 AM
  • Morning,

    @sassan any input ? re settings as above ?

    Friday, July 10, 2020 7:23 AM
  • Hi,

    I would suggest you to first check if backups are working fine from SQL engine itself. Use below Query to initiate a backup from SQL:

    BACKUP DATABASE [DBName]
    TO DISK = N'C:\DB\full'
    GO
    BACKUP LOG [DBName]
    TO DISK = N'C:\DB\full'
    GO

    Change the drive letter as per your requirement and report back with the results. Thanks. 

    Friday, July 10, 2020 7:43 AM
  • I will get the DBA to run that and report back..

    A couple of questions though, I need to know the correlation between the SQL DBA run backups and DPM.

    From Leons and Sassans posts

    "The problem seen above "BACKUP LOG cannot be performed because there is no current database backup." eventually stumps junior DBAs sometimes. This scenario is typically seen when the recovery model of a database was changed in production (typically from full to simple to shrink an overgrown log file - then back to full without taking an immediate full backup after changing back to the full recovery model) The switch from full to simple invalidates the prior existing transaction log backup chain. The subsequent switch back to full recovery model requires a full backup to be taken so the following transaction log backups have a point of reference. In DPM this error typically self corrects like it does in SQL server agent jobs - as soon as a full backup has been taken the transaction log backups will work again. "

    The DBA I understand uses their SQL tools to create SQL backups, and whatever other SQL tasks.

    I have asked what times these are and what the Recovery model for each DB is.

    If the Recovery models do not change you are saying the error will self correct ? Is it a DPM backup timing issue?, do I need to run Express Full Backups at a certain time and day to work around the SQL DBA (SQL Tools) run backups ?



    Friday, July 10, 2020 8:55 AM
  • If you're running SQL backups also from within SQL itself it may interfere with DPM, then I would suggest you to choose, either perform backups from the SQL or from DPM.

    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, July 10, 2020 8:59 AM
  • Lets first understand how DPM takes SQL DBs  backups(For full Recovery Model):

    If we talk about SQL Log backup : DPM does not do anything fancy. It simply runs a SQL Query against the SQL DB which needs to be backup and requests to dump Log files to DPM_SQL_Protect folder located in the Data directory of SQL drive.

    DPM_SQL_Protect Location Screenshot:

    Below is the SQL query it uses to take Log backup from SQL.

    BACKUP LOG [DPMDB_DPM2019] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT\DPM2019\MSDPM2012$DPMDB_DPM2019_log.ldf\Backup\Current.log'

    Once T-Log is dumped in the DPM_SQL_Protect folder, DPM moves it to the DPM replica volume:

    06CC	1AA8	07/10	09:15:04.694	31	sqlbackuphelper.cpp(762)	[000000D0D647EA40]	384609AE-6BC9-4D5A-94FB-6EBE3C766BE6	NORMAL	MovingLongFileName \\?\C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT\DPM2019\MSDPM2012$DPMDB_DPM2019_log.ldf\Backup\Current.log to \\?\C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DPM_SQL_PROTECT\DPM2019\MSDPM2012$DPMDB_DPM2019_log.ldf\Backup\0000000039000012659200001-44022-0937962963000.log

    For SQL DB Full Backup: DPM creates a snapshot on the SQL server and bring over .MDF and .LDF files. This job is called Expressfull backup from DPM prospective. 

    So when you first setup a protection group from DPM, it runs a IR (Initial Replica) job which is equivalent to a full backup which you are referring. So full backup is always taken first by DPM followed by incremental log backup.

    This is the reason I had ask you to take the SQL DB backup from SQL to make sure DB backup and Log backups are working. If it is failing at SQL side, you need to fix that first before DPM backup job can run.


    Friday, July 10, 2020 9:37 AM
  • Your DPM Express full backup is the equivalent to a SQL full backup a DBA would perform (from an overall strategy it were great if backup admins understood SQL and SQL DBAs understood DPM) Thus in your case even if the syncs (DPM for SQL transaction log backups) were failing sometime during the day since someone had changed the recovery model of the protected db in production from full to simple the DPM syncs should work again after the next express full backup has been performed. If the syncs still fail after a DPM express backup of the respective db has been performed - it typically indicates that the db is in simple recovery mode still and the dba has not set the db back to full recovery mode (this can be for various reasons).

    HTH 

    Sassan


    Sassan Karai

    Friday, July 10, 2020 8:05 PM