How to merge database files (Merge multiple ndf files)

Source: Internet
Author: User

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.

 

 

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.