Sql-server – After trying to restart moved database: Unable to open the physical file “D:\Data\MYDBNAME.MDF”

You are getting a permissions error. The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files.

You need to give the account which is running SQL Server (not your account) full control of C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA.

The transaction is being rolled back in the DDL trigger, PreserveTrigger, not the trigger you're trying to drop, trg_write_something. This has nothing to do with the code in the table-level trigger, and nothing to do with permissions either. A sysadmin doesn't get to blindly bypass things like DDL triggers, though it is possible for the code in the trigger to have been written to check the user's security level before deciding what to do (but that is uncommon). It is, of course, very easy for a sysadmin to disable or drop a DDL trigger, obviously.

You're seeing these specific errors because a user-defined error message is being raised within the DDL trigger, followed by an explicit ROLLBACK TRANSACTION;. In a comment above, I mentioned that it could be because of a runtime error, but that is not enough to generate the pair of error messages you are receiving (or force an explicit rollback, unless you somehow managed to get a DDL trigger to raise a severe enough error level to terminate the batch - 16 wouldn't do that).

You can discover this DDL trigger as follows:

SELECT name
  FROM sys.triggers
  WHERE name = N'PreserveTrigger'
  AND parent_class_desc = N'DATABASE';

Or all DDL triggers like this:

SELECT name
  FROM sys.triggers
  WHERE parent_class_desc = N'DATABASE';

(You can also find them in Object Explorer under Programmability > Database Triggers.)

And you can see the code this way (which you should probably do before you drop it - maybe it's there for a reason, even if it is currently standing in your way of dropping this specific trigger):

SELECT m.[definition]
  FROM sys.sql_modules AS m
  INNER JOIN sys.triggers AS t
  ON m.[object_id] = t.[object_id]
  WHERE t.name = N'PreserveTrigger';

Or by right-clicking in Object Explorer and selecting Script Database Trigger As > CREATE To > New Query Editor Window.

You can disable the trigger temporarily like this...

DISABLE TRIGGER PreserveTrigger ON DATABASE;

...which, again, is probably better/safer than just dropping it.