Move from a multiple file database to single file with backup+restore

  • I know this is the opposite of what most people do, but we have a database which it turns out is not used as much as anticipated. It was originally built with multiple database files, MDF + NDF's, and for simplicity, we would like to move this db to another server but use a single database file instead of the multiple files.

    When I backup and attempt to restore the database to another server it asks for me to confirm the location of the multiple db files.

    It is possible to backup the multiple file db and restore to a single file database? Or should I be doing this another way? Is it even possible?

  • I am afraid you cannot use backup\restore to change the number of files. All you can do is change where those files are restored to (a single drive from multiple drives for instance)

    do you have multiple filegroups or just multiple files, or a mixture of the two?

    ---------------------------------------------------------------------

  • george sibbald (1/4/2012)


    I am afraid you cannot use backup\restore to change the number of files. All you can do is change where those files are restored to (a single drive from multiple drives for instance)

    do you have multiple filegroups or just multiple files, or a mixture of the two?

    Hi George

    There is one primary filegroup.

    This contains:

    1 x MDF file

    6 x NDF files

    1 x LDF file

    All these files are on the same logical disk in the mssql\data folder.

    Would you know another way of achieving our aims?

  • You'll have to create a new database with just the single data file and export / import the data from the old database to the new one. There are numerous utilities out there that will do this. If you don't have alot of internal RI the easiest method would be the import/export SSIS wizard.

  • depending on your RAID level having the multiple files on the same drive is not buying you much anyway performance wise.

    arrange some downtime.

    BACK THE DATABASE UP! this is your recovery point if it goes wrong.

    look up dbcc shrinkfile with the emptyfile option to move the data out of the ndfs, and then alter database remove file once they are emptied.

    backup the database at points in the process if you want.

    Make sure the mdf has autogrow on or pre-size it to the size it needs to be to hold all of the data (preferred)

    BACK THE DATABASE UP.

    ---------------------------------------------------------------------

  • Richard Moore-400646 (1/4/2012)


    You'll have to create a new database with just the single data file and export / import the data from the old database to the new one. There are numerous utilities out there that will do this. If you don't have alot of internal RI the easiest method would be the import/export SSIS wizard.

    thats another option, its down to size, complexity and what you are comfortable with.

    RI definitely comes into this method, and don't forget other objects such as users, stored procs, views, functions.

    ---------------------------------------------------------------------

  • Thanks for the help both of you.

    I had already started an import in to a single-file db using the Import and Export wizard, but seems to be taking forever (79000 out of 948000 rows in a couple of hours).

    I will look at the other method and see how it goes.

  • Hi Guys,

    Mind if i jump in on this...its bit of a learning curve for and its a question for

    both of you and its sort of related and since you seem to be on the know on this.

    So bear with my ignorance

    All my experience to date has been on creating a database on a single

    primary group i.e single mdf and ldf. So its quite easy to move the database

    to a new server using backup/restore etc.

    taking your example :

    There is one primary filegroup.

    This contains:

    1 x MDF file

    6 x NDF files

    1 x LDF file

    Assuming they all exist on the same drive

    My question is :

    When you take a backup, i assume it backs up all the data from all the files

    into a single bak file and internally it must in some header logically store

    the file group information and which data lies on which file.

    If you wish to restore this database to another server

    Would have to re-create the database first with the exact structure before attempting

    the restore ? Or is it smart enough to work it all out and as long you have the same

    directory structure it will it do the business

    I suppose my question is what is the de-facto/best practice way of getting this database

    moved

  • Hi Robin,

    A standard restore would attempt to create the database files at the same paths as the original files.

    You can use the WITH MOVE option in the RESTORE statement to give the files new paths if required.

    No need to create the original database first.

    Cheers

  • robinrai3 (1/5/2012)


    Hi Guys,

    When you take a backup, i assume it backs up all the data from all the files

    into a single bak file and internally it must in some header logically store

    the file group information and which data lies on which file.

    If you wish to restore this database to another server

    Would have to re-create the database first with the exact structure before attempting

    the restore ? Or is it smart enough to work it all out and as long you have the same

    directory structure it will it do the business

    the backup stores all the information about the file structure. You do not need to create an empty database before restoring a database. As long as the space is available and a matching directory structure is set up, the restore will succeed.

    So the following simple command is enough to restore a database no matter how complicated the file structure

    restore database yourname from disk = 'path to backup'

    In other words having multiple files does not have to make your backup\restores complicated.

    If you don't have a matching directory structure you would need to add a 'move' clause to the restore command for each file which did not have a matching drive\directory.

    note : its best to start new questions in a different thread.

    ---------------------------------------------------------------------

  • Hi George/Gazareth,

    Thx for the prompt reply...point taken George...its just that it was so closely related...

    will do next time

  • OK thanks to the help of you guys I have successfully completed this task.

    For anyone else that is interested and may need to perform this task in future here is what I did:

    Set all the database NDF files to "limited growth" which I set at 10GB as some of my files were just under this size.

    Made a note of all the NDF database file names

    Now for each of the NDF's, I had Data9..8..7..6 down to 1....Started with data9

    dbcc shrinkfile('MYDB_Data9',EMPTYFILE)

    This took a good few hours to complete. Once completed I ran it again just to be sure, it usually complained that there was nothing to delete.

    Then I ran: dbcc showfilestats

    This showed the MYDB_Data9 was empty

    Then I did:

    use MYDB

    alter database MYDB remove file MYDB_Data9

    This removed the last file in the set. I then repeated all of the above for Data8, Data7 etc until I was down to just the MDF file.

    I did around 1-2 data files per day due to it being a Live system and shrinkfile taking forever...but got there in the end.

  • thanks for posting that back.

    One thing I forgot to mention it would now be worth defragmenting your indexes as the move about of the data is likely to have fragmented them.

    ---------------------------------------------------------------------

  • Thanks, and good work 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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