-1

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.

3

Rather than the detach/attach, take the database offline, change the location, move the file, and set back online. Example DDL:

--take database offline
ALTER DATABASE test SET OFFLINE;

--modify the file location
ALTER DATABASE test MODIFY FILE ( NAME = 'log', FILENAME = 'd:\test2.ldf' );

--manually move the file to the new location

--finally, set database online
ALTER DATABASE test SET ONLINE;
2
  • First offline and then renaming/modify file works. If you have a normal (non-standby/readonly) you don't need to offline it first. With a standby database you must offline it first. Documentation said i need to alter modify file first : docs.microsoft.com/en-us/sql/relational-databases/databases/…
    – user189695
    Jul 22 at 12:44
  • @user189695, seems the doc doesn't cover the standby use case. The order of the offline/modify doesn't normally matter but does in the case of standby.
    – Dan Guzman
    Jul 22 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.