Unable to attach database

Question

Unable to attach database

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

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 SSC Eights! Points: 827 More actions · Answer 2

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: 1003853 More actions · Answer 3

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.

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 SSC Eights! Points: 827 More actions · Answer 4

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: 398679 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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.

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: 7332 More actions · Answer 7

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'