SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
spacedmonkeys
spacedmonkeys
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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?
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23416 Visits: 13698
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?

---------------------------------------------------------------------
spacedmonkeys
spacedmonkeys
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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?
Richard Moore-400646
Richard Moore-400646
SSChasing Mays
SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)SSChasing Mays (636 reputation)

Group: General Forum Members
Points: 636 Visits: 1640
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.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23416 Visits: 13698
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.

---------------------------------------------------------------------
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23416 Visits: 13698
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.

---------------------------------------------------------------------
spacedmonkeys
spacedmonkeys
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 10
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.
robinrai3
robinrai3
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 367
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
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7454 Visits: 6045
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
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23416 Visits: 13698
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.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search