Moving NDF from one Database to another DB

Question

Post reply

Moving NDF from one Database to another DB

thomas.garay

Mr or Mrs. 500

Points: 568
More actions
August 27, 2009 at 7:56 am

#204102

Hi
I need help in figuring out how to move an NDF file from a DB to a totally different DB. We have a 3TB DB with multiple NDF files that store monthly Data. Each NDF is for one month and they are about 300gb - 500GB in size. But after 4 months we need to archive away the oldes month. Dose any one know who this could be accomplished without Reading all the data from one DB into another?
Regards
Thomas

Viewing 15 posts - 1 through 15 (of 15 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

thomas.garay (8/27/2009)
I need helkp in figuring out how to move an NDF file from a DB to a totally different DB.

Cannot be done.

You will have to read the data from one db and insert into the other. Use a tool that does bulk-loads and use the bulk-logged or simple recovery on the destination DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thomas.garay Mr or Mrs. 500 Points: 568 More actions · Answer 2

Hi

It must be able to be done some how...

I can't believe that no one ever thought of doing this?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

It can't be done, and you are not understanding what an NDF is. It's a file, and not necessarily consisting of one object.

You have to export/import the data from one database to the other.

thomas.garay Mr or Mrs. 500 Points: 568 More actions · Answer 4

Our NDF's are in their own individual FILEGROUPS. So it's a one FileGroup TO ONE NDF. Each NDF File contains all their own objects. No object is plit between different NDF's. So, basically they are self contained.

The only reffernece to the NDFS are in the MDF and this is where I believe that I could make the change. but I don't know how.

If I backup one ofthe Filegroups (containing it's self contained NDF) I can't then restore it to an other DB because it tells me that this NDF dose not belong tho the NEW DB.

Dose this make sense?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

thomas.garay(8/27/2009)
It must be able to be done some how...

It's not possible. Files and filegroups are intrinsically linked to the databases that they are part of and there is no way to move a file or filegroup from one database to another.

There's no modification of the mdf that you can do, no changes to the system tables (not that they can be changed on SQL 2005), no alter database statement

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I'm curious how this would be done considering the metadata for the table(s) or table partitions is contained in the system tables in the primary file group in the .mdf file.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

If that NDF file represents a filegroup, and you know what the filegroup contains, then you can, from SQL, copy/move the objects in that filegroup to the other database, but you cannot move the file itself.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

thomas.garay Mr or Mrs. 500 Points: 568 More actions · Answer 8

thomas.garay

Mr or Mrs. 500

Points: 568

August 27, 2009 at 9:05 am

#1045613

OK... Thank you all for your replys.

thomas.garay Mr or Mrs. 500 Points: 568 More actions · Answer 9

Hi

I'm going to try the OBJECT COPY from one NDF to the other DB NDF. Could some one help me with that?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

I would suggest either SSIS or bcp (if it's to be a repeated task) or the import/export wizard if it's once-off.

With bcp you'd have to bcp the data to a file, then bcp it back in. SSIS can be complex to set up. The import/export wizard can save the package that it creates and allow you to modify/schedule it

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

A third option is to use a third party tool like RedGates SQL Compare and Data Compare.

However, if this is going to be a repeatable procedure, I would take the time to create a BCP or SSIS process that can be scheduled to do the task.

And as Gail mentioned with the import/export wizard - if this is a one-time deal - use the import/export wizard. The wizard could be the fastest solution for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 12

thomas.garay (8/27/2009)
Hi
It must be able to be done some how...
I can't believe that no one ever thought of doing this?

While it is true that it is not quite possible to simply move a database file from one database to another, it is possible to simulate it, as part of a backup strategy.

The basic idea is to perform file-based backups and do a piecemeal restore to the archive database. The piecemeal restore involves a one-time restore of the PRIMARY file group, followed by a file restore of the file group you want to archive.

This (and other file groups) can be kept up-to-date using partial differential and log restores. If done correctly, this allows you to effectively remove any number of original files (the NDFs) from database A to database B, as the need arises over time.

There are a number of details to be aware of with this technique, but it can deliver near-instant archiving, and a (read-only) rolling archive of your data.

In case it is of interest to you, or others following this thread, I will post a demo script to illustrate how it works. I have a film to watch first, however 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 13

The demo script I promised is attached to this post.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 14

thomas.garay (8/27/2009)
It must be able to be done some how...

Probably you are confused with Oracle transportable tablespaces - even there you cannot move around a single datafile.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.