开发者

Remove (merge) SQL Servers' database secondary data file

I have a database backup for which SQL Server Management Studio says that it has three files in it: an .mdf file, an .ndf file and one .ldf file. This secondary data file (the .ndf one) was created for no obvious reason, so I want to remove it al开发者_开发问答together (without losing data, of course), preferably during while the database is being restored from the backup.

Is this at all doable?


Ok, found a solution.

First back up the database.

Execute this:

USE database_name;

Then execute this, and replace logical_ndf_file_name with the logical name of your NDF file (which you can easily find out via Database->Properties_Files):

DBCC SHRINKFILE('logical_ndf_file_name', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logical_ndf_file_name;


I ran the empty followed by ndf drop during produciton load successfully. I think it is important to run the drop ndf in the same transaction as the empty to ensure the database doesn't try to write to the file you are deleting, but then after an empty the database marks the files unusable, evidenced by attempting another empty shorty after.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜