Combine Multiple MDF Database Into Single MDF File

  • I have a database that was split years ago into multiple MDF files. Now, with moving to a new server, I'd like to combine all of these MDF files into one (they weren't that large to begin with).

    Does anyone know a good way to go about this? Can it be done?

    Thank you.

    Rog

  • I suggest you do this during a maintenance window while there is no-one using the DB. Emptying the file could take a long time, depending on the data size. Make sure you have a good backup before you start!

    USE dbname;

    DBCC SHRINKFILE ('LogicalFileName', EMPTYFILE);

    ALTER DATABASE dbname REMOVE FILE LogicalFileName;

  • David Portas (7/28/2009)


    I suggest you do this during a maintenance window while there is no-one using the DB. Emptying the file could take a long time, depending on the data size. Make sure you have a good backup before you start!

    USE dbname;

    DBCC SHRINKFILE ('LogicalFileName', EMPTYFILE);

    ALTER DATABASE dbname REMOVE FILE LogicalFileName;

    Thanks for the info. This almost looks too easy (though I don't know how long it will take). Want if they have several mdf files file1.mdf, file2.mdf, file3.mdf, file4.mdf.... how to incorporate all of those into the above?

    Roger

  • Do it one by one for each file.

    Manu

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply