SQL Server 2012 notes Sharing-54: Database File Management 1

(i) Add a file

Can be executed online, without affecting the database usage.

650) this.width=650; "title=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px "style="; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201407/14/639838_1405319926cesw.png "height=" 484 "/>

After the add is complete.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201407/14/639838_1405319927je57.png "height=" 302 "/>

It can also be added in the form of a script.

650) this.width=650; "title=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px "style="; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201407/14/639838_1405319930vwro.png "height=" 166 "/>

(ii) Deletion of documents

can be executed online without affecting database usage.

Successful execution occurs only if the actual space used in the file is empty.

(iii) Move files to different disk paths

The database must be taken offline, causing the database to be temporarily unavailable

Detailed reference: http://msdn.microsoft.com/zh-cn/library/ms345483.aspx

To move the data or log files as part of a planned relocation, follow these steps:

1. Run the following statement.

ALTER DATABASE database_name SET OFFLINE;

2. Move the file to a new location.

3. For each file that you have moved, run the following statement.

ALTER DATABASE database_name MODIFY FILE (name = logical_name, FILENAME = ' new_path\os_file_name ');

4. Run the following statement.

ALTER DATABASE database_name SET ONLINE;

5. Verify the file changes by running the following query.

SELECT name, physical_name as Currentlocation, State_desc

From Sys.master_files

WHERE database_id = db_id (N ' <database_name> ');

is a process that moves the database from the default location to the data folder of the C drive.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201407/14/639838_1405319930sna0.png "height=" 153 "/>

You can see that the move was successful by querying the following statement.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201407/14/639838_1405319931tv7e.png "height=" 242 "/>

It can also be viewed through the graphical interface.

650) this.width=650; "title=" to "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201407/14/639838_1405319931it5c.png "height=" 279 "/>

=================================================================

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!