Moving Database Files to New Disk Deive

Question

Hello,
We have Sql Server 2008R2 and one of the database is more then 2 TB and we can't add any more space on current disk drive so adding new disk drive on the server what will be the best option to move files to new location?

Is it better to move all the files to new drive?
Is it just add another new data files to new disk drive will be better so new data will load directly into new drive location?
Detach/attach?
Backup/Restore?

I am just trying find the best way to avoid or minimize down time as we are at the stage that current disk drive is almost full and can't expand the space but adding another disk drive will make which way better solution?

Currently, We have data file structure like data1.mdf, data2.ndf, data3.ndf, data4.ndf and log file is log1.ldf - all are in PRIMARY GROUP.

Any better solution/suggestion appreciated.

Thanks for your help!

Answer 1

A little off topic but absolutly neccessary to say ;-)
You are running an very old version of SQL Server that is years out of support...

Now to your question:
The easiest way would be, just adding another disk to the system (e.g. drive m:\data) and put additional data file(s) on that drive
The correct and better way - from a performance pov - might be giving each file its own drive (maybe you already have something like this)

drive e:\data => data1.mdf
drive f:\data => data2.ndf
drive g:\data => data3.ndf
drive h:\data => data4.ndf
drive I:\data => data5.ndf
drive j:\tlog => log1.ldf

So process would be : add disks, detach database, shutdown SQL service, move files to separate disks, start SQL services, attach database

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Answer 2

Thanks Peters.

Actually We are tied up with application and some other things so we can't upgrade currently as we have migrated few other servers but I really agreed with you that this is totally out of support.

We have all the Data files and log files on same drive currently.

Is it viable option if I just add the another disk drive on same server and add data file without moving files or detach/ Attach?

Ex. E: drive has E:\data1.mdf, E:\data2.ndf, E:\data3.ndf, E:\data4.ndf, E:\log1.ldf
So If I add another disk drive called F:\ and add data file called J:\data5.ndf, J:\data6.ndf, J:\data7.ndf then it will work so I don't have to move the files or detach/attach?

Answer 3

Hi @pdsqsql ,

Welcome to Microsoft Q&A!
First of all, I'll repeat that this version of sql server is too old to be supported. Now I'll answer your question.
Endorsed by Bjoern Peters. Spreading out files across disks is a great way to do that. In the case of data files, if new data is written, they grow in proportion to the remaining space of each data file. That is, it doesn't grow on a data file so much that it runs out of space.
Please refer to this document: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16#filegroups

so I don't have to move the files or detach/attach?

You need to keep them in the same filegroup.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thanks Seeya.
I am totally agreed about the Sql server version is out of support.

I am somewhat confused regarding adding data files.

So currently I have all my data files and log file is on the same disk drive E:\ and E:\ drive is getting almost full so If I add another disk drive on same server and add Data files like J:\Data5.ndf, J:\data6.ndf (assuming my new drive is J:\ and it has 1 TB disk space ) so whenever new data will be added it will most likely spread proportionally into all the data files but if any data files gets filled up in E:\ drive, it will skip ( I know my monitoring tool/script will alert me that data file is full) and add into J:\ drive.
so this is the right way to do it?

Answer 5

As they say: it depends.

Just adding new files to the filegroups is the easy way out and with minimal downtime.

But best practice is to have all files of the same size, being equally filled etc. But that is a lot more work. And more downtime.

So, yes, I would just add the new files. And possibly turn off autogrow on the the existing files, so that SQL Server gets bad ideas.