I want to move a ldf file from one drive to another. Some idiot who is definitly not me restored the log file to the wrong drive which is nearly full now.
The database is readonly/standby for a logship environment.
Alter database .. modify file does not work since the DB is readonly. I can not detach/attach because i can not specify the standby file with an
create .. for attach.
If i use alter database :
Msg 5004, Level 16, State 4, Line 9 To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.
If i try attach :
Msg 1824, Level 16, State 1, Line 11 Cannot attach a database that was being restored.
The small test case i made :
-- create ro/standby db restore database test from disk = 'D:\BakMcBakface.bak' with move 'data' to 'D:\test.mdf', move 'log' to 'D:\test.ldf', standby='D:\test.standby' -- try alter ALTER DATABASE test MODIFY FILE ( NAME = 'log', FILENAME = 'd:\test2.ldf' ); -- try detach/attach db EXEC master.dbo.sp_detach_db @dbname = N'test' -- attach db CREATE DATABASE [test] ON (filename=N'D:\test.mdf'), (filename=N'D:\test2.ldf') for attach -- with standby='D:\test.standby'
As a last resort i could do a full restore but this is a multi TB database and i try to avoid that. If i could just attach in standby mode i'm done. I could also modify the sys tables directly but i rather not be the cause of the return of Cthulhu.