Moving the Primary File For Large Databases from One Drive To Another

Peter Jones (BIDA) 131 Reputation points
2025-11-04T18:22:06.5066667+00:00

Hi SQL Server Gurus,

I have an interesting problem and I just want to check the answer I have because I am not really sure the answer I have is the best way to go.

I have two large databases to move from the "D" drive where they were set up some years ago to two new drives. One an archive on HDD and one for current data on SSD. The new SSD is "L" and the archive is "Z" but that does not make much difference. It's a data warehouse we are moving.

I have moved virtually all files using a mixture of copy tables and shrinkfile where new files were created on the target drives for the target data. For large fact tables we have one file per month for data and one file per month for indexes and the data goes back to 2008.

I did not want to try and use a pure backup / restore because the databases are very large and it will take quite a lot of downtime and also we have to map all the files on the drive letters.

Now we are almost done and it is time to move the files in the primary file group. I set it up to move it in the same way as everything else and I get the following message. This means that there is data in the very original primary file in the primary file group that can not be moved.

"DBCC SHRINKFILE: System table SYSFILES1 Page 1:5916111 could not be moved to other files because it only can reside in the primary file of the database. "

I have created new files on the L drive and put them in the PRIMARY file group and expected shrinkfile to move all the data to the new files in the primary file group.

I put this question into co-pilot and the response is the simple unattach and reattach procedure.

"in sql server how can the primary file be moved from one drive to another drive?"

I am not at all convinced this is the best way to go because unattach and reattach is usually just for small databases that you want to move between drives. Not for the primary file of a large database with hundreds of files in it.

So my question really is.

For a multi TB data warehouse going back to 2008 is unattach / reattach for the primary file that was created when the database was first created the best way to move it? (Meaning I don't want to back up the whole database.)

In the past, each time the database has been moved it was with backup / restore to a new server and the downtime was accepted as necessary.

This time we just want to move it between drives and minimise the downtime.

If you have any suggestions, comments, ideas?

I would be very grateful to hear from you and I thank you in advance for your time and effort for your comments.

Best Regards

Peter

SQL Server Database Engine
0 comments
Answer accepted by question author
  1. Erland Sommarskog 128K Reputation points MVP Volunteer Moderator
    2025-11-04T22:16:25.0233333+00:00

    No matter what, you need to copy a lot of data, be that BACKUP/RESTORE, detach-copy-reattach or DBCC SHRINKFILE. But I guess that the advantage of the latter can be done without downtime.

    But at this point you have hit the wall it seems, and your best bet is probably to take the downtime and copy the files to the new disks. But you could first move all user objects in the PRIMARY filegroup to other filegroups with CREATE INDEX WITH (DROP_EXISTING = ON) and do that ONLINE. Then shrink the files in primary without EMPTYFILE, to get them down in size. If there are only system tables, they should be fairly small.

    Probably too late now, but another option would have been to restore the database to a different name, leaving it in STANDBY and then run log shipping until it is time for cutover.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Peter Jones (BIDA) 131 Reputation points
    2025-11-08T22:42:44.73+00:00

    Hi SQL Server Gurus,

    So I have successfully moved the primary files across to a new drive.

    In my work today I did come across the idea of using the restore move command.

    I am not an SQL server DBA and so I did not know about the restore move command.

    I have put an example below.

    The DWH database I am moving is 4+TB and goes back to 2008. We have 2000+ files that make up the database. With that massive number of files you can see why I have taken the approach of creating new files across drives and doing a shrinkfile with empty commands.

    (SQL Server will tell me if I make a mistake and it will not corrupt or damage the database. It also keeps the database up. The customer has 450 stores nationwide and inventory being delivered needs to make it into the DWH on an hourly basis. So a multi-hour down time is not really acceptable even on weekends.)

    However, when I moved one of the smaller databases I noticed that I could generate a script as follows that allows me to move files from the backup file to a target. I did not know about this.

    So...my question is this. If I had tried something like this on a 4TB, 2000+ file data warehouse, how would it manage the indexes?

    When indexes are in the same file as the data they index then SQL Server updates the relative address of the data in the index as it restores the file. But keeping indexes in different files to tables has overwhelming advantages in a data warehouse. And this is where much of the time goes in moving data. When you use Shrinkfile SQL Server updates the indexes as it moves the rows. If the index and the data are in the same file this happens quickly. If the indexes are in a different file this happens slowly.

    So I can't see how a restore would restore a table in one file and an index for the table in another file "quickly". My thinking is that a restore database like this would "run forever". But since I really don't want to test it myself on a 4TB+ data warehouse I was wondering if anyone knows the behaviour of the restore command when you move files and the table data is in separate files to the index data. The indexes have to be updated to point to the new disk and the new file and my guess is that such a restore would take "forever".

    But I might be wrong.

    Have any of you SQL Server DBAs done this before? If so? May I please ask you to be so kind as to share your observations here?

    Thank you for any assistance you may be able to offer on this matter. I really appreciate it.

    Best Regards

    Peter

    RESTORE DATABASE [YourDB] FROM DISK = N'D:\SQL_Backup\YourDB.bak' WITH FILE = 1,

    MOVE N'YourDB' TO N'L:\SQL_Data\YourDB\YourDB_01.mdf',

    MOVE N'FG_YourDB_PROD_01' TO N'L:\SQL_Data\YourDB\P_FG_YourDB_PROD_01.ndf',

    MOVE N'YourDB_log' TO N'L:\SQL_Log\YourDB\YourDB_log.ldf',

    NOUNLOAD, STATS = 5

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.