in SQL2000 , I used to dettach the database , then rename the Log file , then attach the database , and it will be attached successfully making new Log file .
As I am new in SQL2005 , I cannot do the same in SQL2005 , i deattch the database , then rename the Log file , then when I am trying to reattach the database , the SQL2005 is refusing to reattach without log file .
No, I suppose you’re not doing something wrong - actually it should work. That’s the code executed for “Detach” and “Attach”, here also after renaming the Tlog:
EXEC master.dbo.sp_detach_db @dbname = N’Navision’
CREATE DATABASE [Navision] ON
( FILENAME = N’D:\Databases\Navision.mdf’ ),
( FILENAME = N’D:\Databases\Navision_new.ldf’ ), – renamed!
( FILENAME = N’D:\Databases\Navision_1.ndf’ )
But: The error you might get is probably caused by this statement (also executed after the “Attach”):
if exists (select name from master.sys.databases sd where name = N’Navision’ and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [Navision].dbo.sp_changedbowner @loginame=N’NB02\jstryk’, @map=false
I got this little error, too, as the user - in my test NB02\jstryk already exists in the DB or uses another alias (Error 15110). But the actual “Attach” works; if you update the list in SSMS you should see the DB again.
So, it’s not the “Attach” going wrong, it’s the sp_changedbowner thingy (which could be ignored) …