August 27, 2009 at 7:56 am
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
August 27, 2009 at 8:04 am
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
August 27, 2009 at 8:07 am
Hi
It must be able to be done some how...
I can't believe that no one ever thought of doing this?
August 27, 2009 at 8:24 am
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.
August 27, 2009 at 8:34 am
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?
August 27, 2009 at 8:39 am
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
August 27, 2009 at 8:43 am
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.
August 27, 2009 at 8:58 am
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.
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]
August 27, 2009 at 9:05 am
OK... Thank you all for your replys.
August 27, 2009 at 9:42 am
Hi
I'm going to try the OBJECT COPY from one NDF to the other DB NDF. Could some one help me with that?
August 27, 2009 at 9:49 am
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
August 27, 2009 at 10:07 am
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
August 28, 2009 at 5:38 am
thomas.garay (8/27/2009)
HiIt 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
August 30, 2009 at 3:15 am
The demo script I promised is attached to this post.
Paul
August 30, 2009 at 8:45 am
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.Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply