LDF and MDF file transfer for SQL Server

After a closer examination, it is found that there are too many MDF and LDF files in the Mssqldata folder, and several are several G-sizes. This is actually the two files.

Usually a variety of restore library, also do not pay attention to this, did not expect the consequences so serious, so the search on the Internet to study how to transfer these files. To sum up:

Like my library test_db.

/* Set Offline */

ALTER DATABASE [TEST_DB] set offline

/* Logical Move/*

Use master;

Go

ALTER DATABASE test_db

Modify File

(

Name = test_db,

filename = N ' f:mssqldatatest_db.mdf '

);

Go

ALTER DATABASE test_db

Modify File

(

Name = Test_db_log,

filename = N ' f:mssqldatatest_db_log.ldf '

);

Go

Take the database offline first so that you can move the two files.

The first logical move, the white is to change the properties of the page in the back of the two paths, will find that these two paths are not in the property directly to change the hand ... So we have to use this statement to change.

Name= is followed by the logical name of both, which is just the property page. is generally the database name and database name _log, but if it is a restored library, the name may be strange, pay attention to the execution of the statement before the logical name change. Logical names can be modified directly in the Properties page.

filename = The path to be moved to is followed by.

After the execution of the above statement, go to the Mssqldata folder to find the corresponding two files, cut to the previous modified path. Note that the filename should also be able to correspond to the.

The last step is to cancel the offline and perform:

ALTER DATABASE [TESTDB] set online;

If the previous name is correct, the path is correct, the file name is correct, and the file is correct, then you can cancel the offline and continue using the library.