"Is it best practice?" NO!!
WHY? – If your database file size is1TB or around 1TB it is not a best practice. So here is the question how to move the database if the database files size is large.
Why it not good practice because while attaching the database it will create the New database on new location where we are moving the database log files. So in this case the existing user will be removed from the database.
Actually it’s quite easy.
Please find the below steps-
USE master;
GO
-- Return the
logical file name.
SELECT * FROM sys.master_files WHERE
database_id = DB_ID('MY_test_DB')
AND
type_desc = 'LOG';
GO
-- Offline the
database
ALTER DATABASE MY_test_DB SET
OFFLINE;
GO
-- Physically
move(Copy and Paste) the file to a new location.
-- Now we have
to modify the path specified in FILENAME to the new location of the file on
your server.
ALTER DATABASE MY_test_DB MODIFY FILE ( NAME = MY_test_DB_Log,
FILENAME = 'D:\NewLocation\MY_test_DB_Log.ldf');
GO
-- Online the
Database
ALTER DATABASE MY_test_DB SET
ONLINE;
GO
--Verify the new
location file location.
SELECT * FROM sys.master_files WHERE
database_id = DB_ID('MY_test_DB')
AND type_desc = 'LOG';
