Post reply

.mdf question

  • June 24, 2009 at 4:44 am

    #133379

    hi all,

    In my database i want to create new *.mdf file and i want to transfer selected tables frmo old mdf to new one.Is this possible?

    If yes how to create and how to move data from old mdf to new mdf?

    Thanks,

    Neerav

  • June 24, 2009 at 5:52 am

    #1014559

    create a new database and import the tables form the old database .

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • June 24, 2009 at 5:57 am

    #1014562

    neerav.saini (6/24/2009)

    hi all,

    In my database i want to create new *.mdf file and i want to transfer selected tables frmo old mdf to new one.Is this possible?

    If yes how to create and how to move data from old mdf to new mdf?

    Thanks,

    Neerav

    If i understand you, think you are talking about adding aditional datafiles (segment/partitions). This can be done and relatively eaasily. Why do you want to do this though?

    Or have i misunderstood. Either way look at BOL under 'Data partition'. 🙂

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • June 24, 2009 at 6:04 am

    #1014568

    You can add data file using alter database

    ALTER DATABASE Database_name ADD FILE

    (NAME='Logical_name',

    FILENAME='Physical_name')

    You can move data using

    DBCC SHRINKFILE('logical_name','EMPTYFILE')

    But I dont know wether it works completely for primary filegroup with system meta data

    If you want to move the the file why dont you detach move and attach?

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 24, 2009 at 6:22 am

    #1014585

    My guess (FWIW) is that the question is about moving a table to a secondary filegroup (typically named *.NDF)

    If so, the following MSDN article explains files and filegroups and links to the SQL DDL statements required:

    http://msdn.microsoft.com/en-us/library/ms189563(SQL.90).aspx

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 24, 2009 at 7:20 am

    #1014630

    so you want to create a secondary Datafile (.ndf) and create new table(s) to ndf.

    that is easy. But it would a good practise to add a filegroup and then add the ndf to the new filegroup.. this will help in database maintenance tasks, as backup just the primary filegroup etc... but it depends in your scenario: and the most important part is SQL Server will never know to place any new objects on the secondary file, but you can create the object with the ON clause in the new Filegroup... correct me if i am wrong.

    and regarding moving the Table.. you cannot as per my understanding, you can only specify create table with on clause to create it in the new filegroup.

    Alter Database DBNAME

    ADD FILE

    (

    NAME = NewFileName,

    FILENAME = 'PathToFile.ndf',

    SIZE = xxx,

    FILEGROWTH = xxx

    )

    Maninder
    www.dbanation.com

  • June 24, 2009 at 7:31 am

    #1014635

    Mani,

    You can move a table from one file-group to another by rebuilding the clustered index with the ON clause.

    Non-clustered indexes work the same way but would have to be done separately.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 24, 2009 at 7:42 am

    #1014645

    paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time. 😀

    Maninder
    www.dbanation.com

  • June 24, 2009 at 8:08 am

    #1014666

    Mani Singh (6/24/2009)

    paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time. 😀

    How many do you have ? (To have such problems)

  • June 24, 2009 at 8:17 am

    #1014673

    homebrew01 (6/24/2009)

    Mani Singh (6/24/2009)

    paul is correct, i completely forgot about that.. i should stop thinking about my girlfriends all the time. 😀

    How many do you have ? (To have such problems)

    I really hope we don't go too far down this particular track :doze:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 24, 2009 at 8:46 am

    #1014694

    Hi,

    I have added new file abc.ndf as per your guidlines but how can i move data to new filegroup.

    Sorry but i am new to Sql Server

  • June 24, 2009 at 5:35 pm

    #1014996

    If you want to add a datafile you can do as joe writes or through SSMS.

    Is there a specific reason why you want to move tables to a different datafile?

    If you add a datafile (.ndf) as I understand it the data are spread across the datafiles evenly when you make a rebuild of all the indexes or when adding data, correct me if I´m wrong.

    If you want to determine the fill level of the datafiles before/after a rebuild use the tips Here.

    Regards

    Stefan

  • May 19, 2013 at 7:25 am

    #1616448

    hi,

    1)First Take backup of current database with to NewTestDbNew.bak

    BACKUP DATABASE [oldTestDb] TO DISK = N'D:\NewTestDbNew.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    2) Restore above NewTestDbNew.bak to your required name and required file path.

    RESTORE DATABASE [NewTestDbNew] FROM DISK = N'D:\NewTestDbNew.bak'

    WITH FILE = 1, MOVE N'BACKUPAEMMS_Data'

    TO N'E:\database\NewTestDbNew.MDF',

    MOVE N'BACKUPAEMMS_Log' TO N'E:\database\NewTestDbNew_1.LDF',

    NOUNLOAD, STATS = 10

    GO

  • May 19, 2013 at 8:45 am

    #1616459

    Please note: 4 year old thread. Also backup/restore is not an option for moving some objects from one filegroup to another.

    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

  • May 24, 2013 at 1:39 am

    #1618121

    First of all, you should have a solid reason for moving the table to new file-group most preferably on a different drive. Secondly, you should keep in mind that there could be downtime required. So you have to plan accordingly.

    There are some solutions , but the best solution would depend upon your environment and data structures. If you have that table without any dependencies, without B/LOB data type etc. data, and you do not want to move such data, it will work great. Moreover, if you have ENTERPRISE edition, you could use index build ONLINE option to move the data without a downtime (some conditions do apply)

    Keeping in mind the limitation of moving B/LOB columns etc., non-flexibility of the CREATE WITH DROP_EXISTING clustered index and table data could be huge, my preference is something like (Hope not missing something)

    TAKE BACKUP OF THE DATABASE

    MAKE SURE THE NEW FILE HAS SIZE AT LEAST EQUAL TO THE TABLE

    GET ALL DEFINITIONS RELATED TO THE TABLE (WOULD BE USED FOR NEW TABLE)

    CHANGE THE RECOVERY MODEL TO SIMPLE OR BULK_LOGGED

    CREATE COPY (JUST MAIN STRUCTURE)OF THE TABLE ON THE NEW FILE GROUP

    DROP FOREIGN KEYS IF ANY

    DROP NON-CLUSTERED INDEXES / MANUAL STATISTICS (OFTEN OVERLOOKED)

    CREATE CLUSTERED INDEXANF NOT ANY ON EXISTING TABLE (PREFERABLY IDENTITY COLUMN)

    MOVE THE DATA IN BATCHES (SORTED IF POSSIBLE) TO KEEP THE LOG FILE IN CHECK IN CASE OF SPACE CONSTRAINTS

    BACKUP LOG IF POSSIBLE

    VALIDATE THE DATA IF POSSIBLE

    CREATE CLUSTERED INDEX ON NEW TABLE (IF NOT POSSIBLE BEFORE)

    CREATE NON-CLUSTERED INDEXES / MANUAL STATISTICS

    VALIDATE REFERENTIAL INTEGRITY BY CREATING FOREIGN KEYS IF ANY

    SQL server DBA

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

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