How do I add another .mdf file at a different location and write data to it - SQL Server


Keywords:sql 


Question: 

I have a huge SQL Server archival database (MISArchival): 250GB and it's sitting on a E:\ drive which has only 30GB space left.

I have to purge/move out some more data from production MIS database to this archival database (MISArchival).

Now I have another drive which is I:\ drive and have around 200GB of space.

My question is: how can I make/add another .mdf file on I:\ drive and make sure next time I insert data to this archival database, it starts to write to this new .mdf file created on the I:\ drive and NOT to the E:\ drive ?

Current setup:

E:\Database\Data\BSP_MIS_Archived.mdf
E:\Database\Log\BSP_MIS_Archived.ldf

I need another .mdf file at a different location due to space issue:

I:\Database\Data\BSP_MIS_Archived_2.mdf

Thank you


2 Answers: 

Sorry, but you can't move objets between files throuth some statement. You will need to move the data your self.

Firt of all create a filegroup and add a new file to it at the new drive.

Create a new table with the same script as the original but using the new filegroup move the data using a insert/select comando or BCP/Bulk and rename the table sp_rename

In the end you need to do a shrink at the original file.

If you have more doubts add a comment.



You can add data files from the database properties in SSMS or from code:

use master;
go

ALTER DATABASE MISArchival
ADD FILE 
(
    NAME = MISArchival2,
    FILENAME = 'I:\Database\Data\BSP_MIS_Archived_2.ndf'
);
go

Some things to note:

  • Typically the secondary data files have an NDF extension but that's not required
  • If you don't create a separate filegroup then the extra data files will be placed into PRIMARY
  • You need to consider what the appropriate initial size, max size, and growth requirements are for your database

Ref: MSSQL Documentation