SQL Server: Best practice to Move database from one Location to another location.

Most of  SQL Server professional may know the how to move the database form One location to another location. And most of the professional use the "detach and attach" method but..

"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';