Explain how SQL Server modifies the location of database physical files, SQL Server

Explain how SQL Server modifies the location of database physical files, SQL Server

Preface

We should all know that when SQL Server creates a new database, it will store the data in disk C by default. Once there is more storage data in the database, there will be little space left on disk C. The solution is to migrate physical files that store data to other disks.

The specific process is:

1. Take the existing database offline

ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK IMMEDIATE;

2. Move the database file to a new location.

After the file is copied, right-click the file and choose Properties> Security> Add Authenticated Users to the group or user name to change the group permissions to full permissions. Otherwise, the following operation will report

Possible problems:

The physical file "D: \ MSSQL \ DATA \ testdb. mdf" cannot be opened for message 5120, level 16, status 101, and line 17th ". Operating System Error 5: "5 (Access denied .)". The physical file "D: \ MSSQL \ DATA \ testdb _ log. ldf" cannot be opened for message 5120, level 16, status 101, and line 17th ". Operating System Error 5: "5 (Access denied .)". Message 5181, level 16, status 5, 17th rows cannot restart the Database "ctrip ". Returns to the previous status. The alter database Statement of message 5069, level 16, status 1, and row 17th failed.

3. Modify the point of the database associated file

ALTER DATABASE DB1 MODIFY FILE(NAME = DB1, FILENAME = X:\SQLServer\DB1.mdf);ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = X:\SQLServer\DB1_Log.ldf);

4. Bring the database online

ALTER DATABASE DB1 SET ONLINE;

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.