none
backup sql database RRS feed

  • Question

  • HI all,

    sorry for my englisch and basic questations . I 3 quetstaion about sql backup

    if I have sql simple recovery model  , DPM create full backup . The full backup is t-sq backup and copy to dpm replica ? Or VSS copy block data to dpm ? If this create with vss , copy all data or compare with replica ?

    If I select full recovery model and select just before recovery point it is truncate logs and create full backup ? and if I select synchronization grekvenci etc . 5 hours it is backup log and copy to dpm or how dpm create replica ?

    thanx


    Falcon

    Monday, June 9, 2014 8:28 PM

Answers

  • Hi,

    I can help fill in the blanks for you.

    DPM does not use any native sql backup methods.

    T1 - You protect SQL Database with DPM - the DB is in full recovery model.  DPM creates a replica and recovery point volume. Initial replica job runs and creates a VSS shadow copy on the protected SQL server and copies the SQL databases files to the replica, then creates a local shadow copy on the DPM Server.  You now have a single recovery point.  DPM sets up synchronization and express full backup schedule according to the protection group settings.

    T2 - Incremental Sync is ran. SQL dumps it's log entries into a file under the DPM_SQL_PROTECT folder located where the SQL .LDF file is.  DPM copies that file to the DPM replica volume.  This is the second recovery point for that SQL db.  SQL will truncate the log.

    T3 - Tx - Additional Incremental Sync jobs run, each one copies the log entries dumped by SQL since the last incremenmtal sync and stores them on the replica volume.  Each sync is a seperate recovery point.  SQL will truncate the log after each successful sync.

    Tx+1 a new Express full backup is initiated.  DPM creates a snapshot on the SQL Server and only brings over .MDF and .LDF file changes since the last express full backups and applies them to the DPM replica volume.  DPM then creates a snapshot of the replica and then deletes all the old incremental logs what were transfered since the last express full backup. NO log truncation is done after an express full backup.

    Tx+2 - Incremental Sync is ran. SQL dumps it's log entries into a file under the DPM_SQL_PROTECT folder located where the SQL .LDF file is.  DPM copies that file to the DPM replica volume.

    The cycle continues - every express full backup and incremental sync are recoverable by DPM under the recovery tab.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, June 10, 2014 8:40 PM
    Moderator

All replies

  • If I select full recovery model and select just before recovery point it is truncate logs and create full backup ?

    Don't know a definite answer for all your questions unfortunately, in my experience DPM backups of SQL just tend to work, so not certain about what exactly is happening under the hood in terms of SQL, however...

    In terms of the above question the answer is no. If you check the option for "just before recovery point" then DPM won't actually do a log backup, and the logs won't be truncated (I discovered this the hard way!). The reason is that while this option may be useful for some kinds of backups, with SQL backups DPM basically says "you're asking me to do a full backup AND an incremental backup at the same time! That seems a bit pointless, so I'll skip the incremental and just do the full." So for SQL backups you should always configure that to something else rather than check that option.

    Monday, June 9, 2014 8:47 PM
  • thank you for answer .how work backup full backup ? it is t-sql backup batabase and copy this to dpm ? and how work backup with transaction log ? it is backup log and copy to dpm ?

    Falcon

    Tuesday, June 10, 2014 6:47 PM
  • Hi,

    I can help fill in the blanks for you.

    DPM does not use any native sql backup methods.

    T1 - You protect SQL Database with DPM - the DB is in full recovery model.  DPM creates a replica and recovery point volume. Initial replica job runs and creates a VSS shadow copy on the protected SQL server and copies the SQL databases files to the replica, then creates a local shadow copy on the DPM Server.  You now have a single recovery point.  DPM sets up synchronization and express full backup schedule according to the protection group settings.

    T2 - Incremental Sync is ran. SQL dumps it's log entries into a file under the DPM_SQL_PROTECT folder located where the SQL .LDF file is.  DPM copies that file to the DPM replica volume.  This is the second recovery point for that SQL db.  SQL will truncate the log.

    T3 - Tx - Additional Incremental Sync jobs run, each one copies the log entries dumped by SQL since the last incremenmtal sync and stores them on the replica volume.  Each sync is a seperate recovery point.  SQL will truncate the log after each successful sync.

    Tx+1 a new Express full backup is initiated.  DPM creates a snapshot on the SQL Server and only brings over .MDF and .LDF file changes since the last express full backups and applies them to the DPM replica volume.  DPM then creates a snapshot of the replica and then deletes all the old incremental logs what were transfered since the last express full backup. NO log truncation is done after an express full backup.

    Tx+2 - Incremental Sync is ran. SQL dumps it's log entries into a file under the DPM_SQL_PROTECT folder located where the SQL .LDF file is.  DPM copies that file to the DPM replica volume.

    The cycle continues - every express full backup and incremental sync are recoverable by DPM under the recovery tab.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, June 10, 2014 8:40 PM
    Moderator
  • thank you for perfekt answer ;)

    Falcon

    Friday, June 13, 2014 7:35 PM