Another quick one:

Sometimes when you are try to move data/log files from one location to another you employ the option of moving all the objects from the file and emptying it out. Then using ALTER DATABASE we could try to delete it. But when you do that we run into this error.

[sourcecode language=”sql”]
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
[/sourcecode]

This is because, by default Sql Server will not allow deleting the first data/log file created when you created the database.

Since deleting the primary data/log file is not allowed, we can move the data/log file to a new location by bringing the database offline. This is not a favourite option for everyone, but this is a solution.

• Step 1: Bring the database OFFLINE

[sourcecode language=”sql”] ALTER DATABASE DBADB SET OFFLINE [/sourcecode]

• Step 2: Move the file to the new location.
• Step 3: Add the new file location to the metadata

[sourcecode language=”sql”]

ALTER DATABASE DBADB
MODIFY FILE
(
NAME = N’DBADB_Log’
, FILENAME = N’L:\MSSQL\Log\DBADB_Log.LDF’
)

[/sourcecode]

• Step 4: Bring the database ONLINE

[sourcecode language=”sql”]ALTER DATABASE DBADB SET ONLINE[/sourcecode]

If you have a better solution, please share it in the comments section.

Hope this helps,
_Sqltimes