Post reply

Physical db changes stored in transaction log file?

  • June 5, 2020 at 3:07 pm

    #3760877

    Hello,

    I'm trying to understand what is needed to support a development team when they do their (CRM) sprints.

    Short answer question: are physical db changes stored in transaction logs?

    Meaning, if a dev team makes changes to stored procedures, adds columns, modifies datatypes etc...  ( I'm not talking about data changes to records - but database changes ) - are these modifications captured in the txn log?

    Why?   Because I'd like to know if this development server I have died, if I were to restore a DB from a full backup, and *then the latest txn log* --  would I be able to to recover any of the physical database changes up to the point when the server died?

    It's my understanding that before a sprint, this dev team restores a backup from production to this server, then makes a bunch of design changes... tests, and if they are cool, off they go back to the production server...

    But, if the server were to crap-out in the middle of one of their sprints during the work day, would they be losing their daily work if the server were to die before, say, the nightly full database backup?

    Thanks!

  • June 5, 2020 at 3:12 pm

    #3760880

    By "physical", I take it you;'re referring to DDL changes as opposed to DML?  The answer is yes - DDL and DML changes all go through the transaction log and can therefore be rolled forward with transaction log restores.  You might want to try it yourself.  Create a test database, back it up, create an object in the database and take a transaction log backup.  Now restore the  database from the full backup and see whether the new object exists.  Now restore the database again, but this time also restore the log backup before recovering.  Does the object exist now?

    John

  • June 5, 2020 at 4:19 pm

    #3760899

    John,

    Yes, DDL not DML changes.  I guess then my question becomes: What is considered within the domain of DDL, as far as the transaction log is concerned?    I can't find documentation of what goes in the TXN logs.

    Based on what you are saying, I imagine of course table/column/data types changes would be captured in this DDL in the TXN log.  Got it.

    But I wasn't sure if everything specifically done to a database, such as triggers, stored procedures, permissions specific to objects in this specific database, etc. etc...  would be captured in the TXN log.

    To make my concerns worse, when "developing" this group is using the MS Dynamics CRM  WYSIWYG editor thing.  You know... click, click, click, drag & drop, next, next, finish...   So they have only a partial idea what is happening to which of the various application-created and maintained databases they are working with.    (But now I just sound old and cranky...)   So maybe I just need to turn on TXN backups for every db on the server to an interval that assumes some acceptable amount of loss of work.

    Thank you!

  • June 5, 2020 at 6:29 pm

    #3760935

    I treat my Dev servers exactly the same as my Prod servers when it comes to backups and transaction logs and recovery models.  It's has saved dozens of days of lost development and test setups, etc, to be able to do a PIT restore.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 8, 2020 at 8:03 am

    #3761238

    Yes, it includes all operations done in the database.  There may be some operations done to the database (such as ALTER DATABASE statements, which are run at server level) that aren't included, but I don't know for sure.  As I said before, give it a try.

    John

  • June 8, 2020 at 2:10 pm

    #3761323

    Thanks guys.

    Unfortunately, testing before and after changes would prove troublesome.  I don't have the Dynamics CRM skill, or a clone of the environment to do the testing myself - which is problematic b/c the folks that do the work are in a different timezone, and are paid by the hour to do very specific tasks.   This isn't my team...  they just use a server that I was asked to care about.

    Making this worse... the primary database is ~825GB, and takes about ~2 hours to backup.  It takes about 3hours total to get all the ancillary DBs that also may be affected by CRM's spaghetti bowl of database design.

    I think the point of treating it like a production system is what I'll need to do.   Too bad!  I was hoping to treat this area like a step-child 😉

    Happy Monday!

    Thanks,

    -MG

    (EDIT:  I had the size of the DB waaay off)

    • This reply was modified 4 years, 5 months ago by  madEG.

  • June 8, 2020 at 2:37 pm

    #3761327

    madEG wrote:

    Thanks guys.

    Unfortunately, testing before and after changes would prove troublesome.  I don't have the Dynamics CRM skill, or a clone of the environment to do the testing myself - which is problematic b/c the folks that do the work are in a different timezone, and are paid by the hour to do very specific tasks.   This isn't my team...  they just use a server that I was asked to care about.

    Making this worse... the primary database is 7GB, and takes about ~2 hours to backup.  It takes about 3hours total to get all the ancillary DBs that also may be affected by CRM's spaghetti bowl of database design.

    I think the point of treating it like a production system is what I'll need to do.   Too bad!  I was hoping to treat this area like a step-child 😉

    Happy Monday!

    Thanks,

    -MG

    A 7 GB database takes 2 hours to backup?  I just backed up an 8 GB database on my testing machine that has 8 GB ram and 2 processors, it took 3 minutes and 23 seconds.

    My largest production server is ~1.3 TB of data, it takes 2 hours to a network share, and that time includes verifying the backups.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • June 8, 2020 at 3:51 pm

    #3761355

    Michael,

    I just came back to edit my mistake, and saw your post.  You're right, that would be a terrible time to complete.

    The primary MSCRM db is about 800GB.    All the DBs are ~825 or so.

    The storage that I backup these CRM DBs to (since there is not enough space on the server to write them locally, before copying) is part of the reason it takes that long, I'm sure.

    Sorry about the confusion!

     

     

  • June 8, 2020 at 7:57 pm

    #3761444

    Just to set expectations, I never backup to the server I'm backing up from.  I backup to a NAS.  My 1TB database takes about 30 minutes and I'm not splitting the back up more than 1 backup file.  I AM, however, using compressed backups, which really helps a whole lot.

    Although it seems like I'm bragging, I'm not.  A lot of folks can do even better.  I just wanted to let you know that someone should take a look at the connections between your server and wherever you're backing up to because it seems pretty slow.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply