We help IT Professionals succeed at work.

3456 error when attempting to attach 2005SQLDB to 2008SQLDB

986 Views
Last Modified: 2012-10-19
We had a hard drive crash on a server that had critical data stored in a MSSQL2005 DB.  I have a snapshot of the folder containing the DB files from 12/23 and am attempting to attach to a temp server running SQL2008R2.  During the attachment process, it hangs and eventually gives me a 3456 error (see attached screenshot).  

error message screencap
I have seen a MS Knowledge Base article with a supposed fix for the MSSQL2005 platform (http://support.microsoft.com/kb/937544) but what I'm doing isn't a perfect match given that my temp server is running 2008.

This is a mission critical data, your help is most appreciated.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>This is a mission critical data, your help is most appreciated. <<
Time to put that DR plan into practice and restore from the last good Full backup with NORECOVERY and then all the subsequent Transaction Log backups.

Author

Commented:
Unfortunately, that isn't an option. All I have are the mdf files set as they were on 12/24. I do have access to weekly snapshots prior (12/17, 12/10, etc) but the recovery process scales up with each lost week.  I am looking for specific instructions on how to reattach a db when the automated process doesn't go as planned.
CERTIFIED EXPERT
Top Expert 2011

Commented:
but a snapshot isn't a database backup, and can only be used to restore a database if the database was "detached" when the snapshot process was run...

I don't think your knowledge database article is at all related to the current problem... (it is to do with a lack of space being available to extend the log files...)

was the database detached prior to the snapshot being taken?
what is the physical structure of the database (how many .mdf , .ndf files comprise the database ... how many physical log files does it have,,, what is the logging method?)

as Acperkins stated ... if its mission critical , then go for your DR strategy now. and do it on a proper sql 2005 installation.



CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I am looking for specific instructions on how to reattach a db when the automated process doesn't go as planned. <<
And what we are trying to convey to you is that you may out of luck.

I am sorry to be the harbinger of bad news.
CERTIFIED EXPERT
Top Expert 2012

Commented:
But before you go and update your resume, you may want to try and attach the database using T-SQL. It may provide additional info.  Alternatively you may be able to attach just the data file, with the understanding that there will be data loss.  Let me know if you need additional info.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Since you don't have the log files, your best chance is to tell SQL to rebuild a new log file, rather than it doing its normal process of recovering the db.

So either:

EXEC sp_attach_single_file_db '<db_name>',
    'd:\drive\and\path\to\primary\filegroup\filename.mdf'

Or:

CREATE DATABASE <db_name>
ON ( NAME = <logfilename>, FILENAME = 'd:\drive\and\path\to\primary\filegroup\filename.mdf' )
FOR ATTACH_REBUILD_LOG

Personally I never attach through the GUI, but I took a quick look and I don't see a way to specify those options thru the GUI.  I'd recommend using a command for this anyway, because you will want to know later *exactly* what commands you issued.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You will certainly lose any transactions that were in-flight at the time.

You might even lose committed trans, and that could vary db by db (SQL guarantees writes to the log when committing, not to the data).

Author

Commented:
Thanks everybody for the feedback and input, as it stands, I was able to attach an older DB snapshot from 12/15 and also was able to attach the 12/24 snapshot but only by pulling a bait-and-switch on the logfile by using the previous available version of the file, however this technique caused some data loss in my transaction table that went back a few months and thus, that build cannot be trusted/used over the 12/15 build that had no such issues.  I got my replacement drives for the server in this morning and will be able to do further testing in my 2005 SQL environment later this morning/afternoon and will keep everyone posted on what I wind up doing.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.