Post reply

Adding multiple secondary data files

  • January 5, 2010 at 11:20 am

    #156549

    Hi,

    We have SQL Server 2005 Enterprise editin 64 bit with SP3 which is having Sahre point databases. Initially the content database (share point database name) is created with one data file & one log file while installing Share point.

    Now as the content database size is increased to 50 GB, I want to create two secondary data files on same existing file group on different drive.Here auto growth is enabled and set to increase by 100 MB.

    Questions:

    1. If auto growth is enabled, then can we create the secondary data file?

    2. After creating the secondary data file, do we require to move any tables to the new secondary data file?

    3. After just creating the secondary data file, if I do not do anything, then how the data will be distributed to secondary data file from primary data file?

    4. If we have multiple secondary data file, can we back them separately and when required can we restore them separately and bring the database online, in case if one of the secondary data file corrupts?

    please advice me

    thanks

  • January 5, 2010 at 11:46 am

    #1099123

    You can find answers to a lot of your questions in this forum post:

    http://www.sqlservercentral.com/Forums/Topic679453-146-1.aspx

    I did not have any trouble adding secondary data files (.ndf) with autogrow turned on, and have seen data populate those files without having to do anything beyond creating the files.

    I could be mistaken, but I do not believe you can back the .ndf files separately.

    Hope this helps a little.

  • January 5, 2010 at 11:52 am

    #1099130

    If you wish to create a separate backup for different ndf files, you will need to explore multiple filegroups. With multiple filegroups you can do piecemeal backup and restores.

    Just keep in mind - that the backups for files that reside in the same filegroup will include all files in that filegroup.

    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

Viewing 3 posts - 1 through 2 (of 2 total)

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