How to merge database files (Merge multiple ndf files)

There is a major database file EmployeeManagement. mdf in a database and two secondary database files rt. ndf and YU. ndf. There is also a transaction log file EmployeeManagement_log.ldf.

1:

Now we want to merge the data in rt. ndf and YU. ndf to EmployeeManagement. mdf.

Here we use the method of shrinking database files to merge database files.

Command for shrinking database files:

 

DBCC SHRINKFILE(    { 'file_name' | file_id }    { [ , EMPTYFILE ]    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]    })[ WITH NO_INFOMSGS ]

Here is a description of the parameter EMPTYFILE:

EMPTYFILE

Migrate all data in the specified file to other files in the same file group. SQL Server 2005 Database Engine does not allow data to be stored in files used for the EMPTYFILE option. This option allows you to use alter database to delete files.

It means to execute the contraction command First: Dbcc Shrinkfile (file name, Emptyfile)

Execute the command to delete the Database File: Alter Database name Remove File name

 

Next we will perform a test:

 

Use EmployeeManagement
Go
Set Nocount On
Declare @ DBFile1 nvarchar (512 ),
@ DBFile2 nvarchar (512 ),
@ SQL nvarchar (4000)

Set @ DBFile1 = 'rt'
Set @ DBFile2 = 'yu'

If Not Exists (Select 1 From sys. sysfiles Where name = @ DBFile1)
Begin
Set @ SQL = @ DBFile1 + N' the database file does not exist or has been deleted! '
Raid error 50001 @ SQL
Goto ExitFlag
End
If Not Exists (Select 1 From sys. sysfiles Where name = @ DBFile2)
Begin
Set @ SQL = @ DBFile2 + N' the database file does not exist or has been deleted! '
Raid error 50001 @ SQL
Goto ExitFlag
End

If object_id ('tempdb .. # ') Is Not Null
Drop Table #

Create Table # (Rows int)

Set @ SQL = Null
Select @ SQL = Isnull (@ SQL + Char (13) + Char (10), '') + 'insert Into # Select Count (*) From '+ Quotename (name)
From sys. objects
Where type = 'U'
If @ SQL> ''Exec (@ SQL)

Select Sum (Rows) As [number of records of all tables before database files are merged] From #

Dbcc Shrinkfile (@ DBFile1, Emptyfile) With No_Infomsgs
Exec (N 'alter Database EmployeeManagement Remove file' + @ DBFile1)

Dbcc Shrinkfile (@ DBFile2, Emptyfile) With No_Infomsgs
Exec (N 'alter Database EmployeeManagement Remove file' + @ DBFile2)

Truncate Table #
Set @ SQL = Null
Select @ SQL = Isnull (@ SQL + Char (13) + Char (10), '') + 'insert Into # Select Count (*) From '+ Quotename (name)
From sys. objects
Where type = 'U'
If @ SQL> ''Exec (@ SQL)

Select Sum (Rows) As [number of records of all tables after database files are merged] From #

ExitFlag:

 

After execution

 

Run OK to check whether the table data is lost before and after database file changes by counting whether the total number of records in the database is consistent.

Counting the number of records in a table is just a simple check method. You can also use backup to restore another database that is the same as it to check whether the data is consistent.

After testing, we can see that the rt. ndf and YU. ndf files have been deleted.