Unable to attach database

  • Jeff Moden

    SSC Guru

    Points: 995683

    Luv SQL wrote:

    Yes it's very clear the backup system is flawed but unfortunately our IT is outsourced and that was all I was given and have access to.  I've requested a new array so at least I can create my own SQL backups to use in the future when needed.

    I was able to attach with a backup of the mdf and ldf files that I found on a new server.

    It's not really that the backup system is flawed.  The idea of making backups of the actual MDF and LDF files is what's flawed.  There's no chance of doing a "Point-in-Time" restore using such a method.

    Do compressed native backups, instead.  The RPO will love you for it.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • oogibah

    Right there with Babe

    Points: 796

    Unless you're using TDE then compression prior to 2016 can be bad. ( or so I've read, I don't care to tempt it myself )

  • Jeff Moden

    SSC Guru

    Points: 995683

    oogibah wrote:

    Unless you're using TDE then compression prior to 2016 can be bad. ( or so I've read, I don't care to tempt it myself )

    Just to give you some confidence in this area, we've been using non-TDE compressed backups since 2015 (that's when we upgraded from 2005 to 2012).  I can't speak for 2008/2008R2 (we skipped over those) but we've never had a problem with compressed backups and I do a couple of restores from backups every single night.  We upgraded to 2016 right after SP1 came out and, still, no problems with compressed backups or restores.

    I would, however, be interested in any links you might still have that have made the claim so that I know what to keep my eye out for.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • oogibah

    Right there with Babe

    Points: 796

    Absolutely: https://support.microsoft.com/en-us/help/4101502/tde-enabled-database-backup-with-compression-causes-corruption

     

    One of the first reasons I came to this site is because I was experiencing a TON of corruption going on in my databases, literally every night, ultimately it turned out the problem was with some backend hardware (ofc), just took forever to figure out the root cause, this was something I investigated along the way.

  • Grant Fritchey

    SSC Guru

    Points: 396339

    Yeah it's not the compression. That's fine. (although I know where you can get even better than native compression <cough> Redgate </cough>), it's the TDE with compression that can lead to issues. Actually, what I've found in all my testing (although, haven't tested 2017/2019, so things may have changed) is that TDE and compression actually makes backups bigger. I wouldn't suggest it without testing it.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995683

    Grant Fritchey wrote:

    (although I know where you can get even better than native compression <cough> Redgate </cough>)

    Sounds like the making of an article, Grant.  A comparison using the same database would be a good one.  I know I'm preaching to the choir but don't forget to include resources used and duration.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Super Cat

    SSCertifiable

    Points: 7314

    As advised you will need to restore to another server.

    There are 3 methods if I remember but I only remember this one.

    If this doesn't work google it.

    EXEC sp_attach_single_file_db @dbname='MyDatabase',

    @physname='E:\Database\MyDatabase.mdf'

Viewing 7 posts - 16 through 22 (of 22 total)

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