January 4, 2012 at 6:00 am
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?
January 4, 2012 at 6:43 am
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?
---------------------------------------------------------------------
January 4, 2012 at 6:50 am
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?
January 4, 2012 at 6:57 am
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.
January 4, 2012 at 6:59 am
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.
---------------------------------------------------------------------
January 4, 2012 at 7:03 am
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.
---------------------------------------------------------------------
January 4, 2012 at 7:14 am
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.
January 5, 2012 at 5:13 am
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
January 5, 2012 at 5:27 am
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
January 5, 2012 at 5:29 am
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.
---------------------------------------------------------------------
January 5, 2012 at 6:36 am
Hi George/Gazareth,
Thx for the prompt reply...point taken George...its just that it was so closely related...
will do next time
January 9, 2012 at 7:38 am
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.
January 9, 2012 at 7:59 am
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.
---------------------------------------------------------------------
January 9, 2012 at 8:34 am
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