Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Move from a multiple file database to single file with backup+restore Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2012 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 4:02 AM
Points: 4, 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?
Post #1229854
Posted Wednesday, January 4, 2012 6:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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?


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

Post #1229877
Posted Wednesday, January 4, 2012 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 4:02 AM
Points: 4, 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?
Post #1229881
Posted Wednesday, January 4, 2012 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:45 PM
Points: 199, Visits: 1,500
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.
Post #1229885
Posted Wednesday, January 4, 2012 6:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


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

Post #1229887
Posted Wednesday, January 4, 2012 7:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


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

Post #1229891
Posted Wednesday, January 4, 2012 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 4:02 AM
Points: 4, 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.
Post #1229902
Posted Thursday, January 5, 2012 5:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:03 AM
Points: 69, Visits: 331
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






Post #1230645
Posted Thursday, January 5, 2012 5:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 1,948, Visits: 3,221
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
Post #1230653
Posted Thursday, January 5, 2012 5:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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.


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

Post #1230655
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse