Attach 2

[sRockyHill]

I dettach the database, and deleted .ldf file. I try to attach database again and gives following error.

Error 1813: Could not open new database 'ReferReports' Create database is aborted.

Device activation error. The physical file name
'D:mssql$surfin\logs\referreports_log.ldf' may be incorrect.


Thank you

 

[NoCoolHandle]

ooo poop..
Do you still have the logs? (in the recycle bin)

Have you tried to use sp_attach_single_file_db..

If you tried sp_attach_single_file_db and it fails, try to get your log back..

If that fail.. It is possible to rebuild status flags in the database and bring it back up, but you definitly risk having corrupt data showing up in your database..

Do you have backups

Rob

 

[NoCoolHandle]

If you need to repare a Corrupt/Suspect database and you don't have good backups and your log is inaccessable..

Follow the steps below.. THis came from pretty high up the SQL team and isn't supported, but it can be used.

1 word of caution. If you had transactions open when the log was deleted then you might have corrupt data.

It has saved my bacon at least once.

Again BE CAREFULL and exaust every other option first!


Rob


1) Make sure you have a copy of your.MDF
> 2) Create a new database called fake (default file locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy your.MDF to where fake_Data.MDF used
to
> be and rename the file to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following (make sure there
are
> no connections to it) in Query Analyzer (At this stage you can actually
> access the database so you could use DTS or bcp to move the data to
another
> database).
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will need them
> in the next bit of the script to replace datafilename and
> logfilename - it might be that they have the right names */
> sp_renamedb 'fake','your'
> go
> alter database your
> MODIFY FILE(NAME='datafilename', NEWNAME = 'your_data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME = 'your_Log')
> go
> dbcc checkdb('your')
> go
> sp_dboption 'your','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the log file will be
> small so it will be worth increasing its size. Unfortunately your files
will
> be called fake_Data.MDF and fake_Log.LDF but you can get round this by
> detaching thedatabase properly and then renaming the files and reattaching
> it.
> 14) Run the following in QA
> sp_detach_db your
> --now rename the files then reattach
> sp_attach_db 'your','your.mdf','your.ldf'
>