LDF and MDF file transfer for SQL Server

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.