SP_Attach_DB Question

[JayKusch]

OK ... here we go

Had a DB named "DB1". Detached DB1, renamed the MDF and LDF to DB1_DataOld.mdf and DB1_Logold.ldf.

Created a new "fresh" copy of DB1. Of course we now have DB1_Data.mdf and DB1_Log.ldf created as expected.

I would like to now "ReAttach" the old DB1 data and log files and rename the database to be DB_Old.

So here is my reattach command:

EXEC sp_attach_db @dbname = N'DB_Old',
@filename1 = N'D:\database\Data\DB1_DataOld.mdf',
@filename2 = N'D:\database\Data\DB1_LogOld.ldf'

I am receiving the error message of :

Server: Msg 9003, Level 20, State 4, Line 1
The LSN (764612:170:1) passed to log scan in database 'DB_Old' is invalid.

Connection Broken


Is there any way to accomplish this?

Thanks

J. Kusch

 

[SonOfEmidec1100]

Ive done similar things before with no problems, I would guess the cause is to do with state of the transaction log at the time you detatched it. You could try to reattatch without the log file, this will create a new empty one, however because of the error there could be implications to data consistancy.

EXEC sp_attach_db @dbname = N'DB_Old',
@filename1 = N'D:\database\Data\DB1_DataOld.mdf'

Someone else maybe have better solution

 

[JayKusch]

As a follow up ... I was first of all dealing with a corrupt/suspect DB that I was trying to attach.

I could not use sp_attach_db nor could I use sp_attach_single_file_db. Both would generate an error and would not attach.

The way I got around this was to rename the current, corrupt, MDF and LDF I was trying to attach.

I then create, through Enterprise Manager, a new database with the same name as the suspect DB I was trying to attach.

When it was created, I stopped the SQL services, deleted the current MDF and LDF and then renamed the corrupt MDF and LDF to the database name I had just created.

When I started the SQL services back up, I was now provided the suspect DB in all its glory. I was able to put it into emergency mode and port out all of the data I needed. I got lucky there !!!

Thanks for the help.

Thanks

J. Kusch