One way to manually move an entire MSSQL database to a new server is to copy the database files: the .mdf (Main Data File) and the .ldf (Transaction Log File).
What are .mdf and .ldf files?
The .mdf file contains all the data and structure of the database, while the .ldf file contains all the transaction records. Some SQL scripts will modify the .mdf file directory. When there is a transaction, the data is first written into the .ldf file, then modifies the .mdf file.
How do you move the database using .mdf and .ldf files?
As mentioned, these two files contain everything related to the database, so we can copy them to the target machine and then attach them to the target MSSQL server. But where are these files located?
How can we find the paths of the .mdf and .ldf files?
To find the file paths, you can use the following command:
EXEC sp_helpfile
How can I copy the files?
However, when trying to copy these files, you might encounter issues because the files are locked by the SQL Server. To resolve this, you need to take the database offline or detach it to gain authorization to copy the files.
-- Set the database offline
ALTER DATABASE SODEVDataDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Set the database online
ALTER DATABASE SODEVBranchJobDB SET ONLINE;
After moving the files to the target machine, how do you attach the database?
After copying the .mdf and .ldf files, you can attach them to the MSSQL server with the following command:
CREATE DATABASE YourDatabaseName ON
(FILENAME = 'C:\YourPath\YourDatabase.mdf'),
(FILENAME = 'C:\YourPath\YourDatabase_log.ldf')
FOR ATTACH
Testing the new database
After successfully attaching the database, you should test it to verify if it runs correctly.