Filegroup Backup


  • Hi

    I have a database that is approx 1.1 terabytes.
    The data is stored in 2 files in the Primary filegroup.

    PRIMARY NapersoftArchive_Data  H:\DataFileGroup4\NapersoftArchive.mdf  458092.00000
    PRIMARY NapersoftArchive_Data_Second I:\DataFileGroup5\NapersoftArchive_Data_Second.ndf 857212.00000

    I would like to move the NapersoftArchive_Data on H:\ to another drive and add another file to the Primary on
    H:\ to pick up space for the current archive. Maybe PRIMARY NapersoftArchive_Data_Third. 
    Maybe I need a secondary filegroup, but this is a stop-gap measure for space reasons.

    Now this may be a bad idea, but is there any way I can backup
    the PRIMARY NapersoftArchive_Data  H:\DataFileGroup4\NapersoftArchive.mdf
    and restore only that file of the primary filegroup to a G:\ drive with a move command?


    martes, 13 de marzo de 2012 17:12

Todas las respuestas

  • Hello,

    You could try something like 

    1. Detach/Attach method.

    2. Backup/Restore whole database with move command.

    3. Make the database OFFLINE/Move file Physically/ALTER DATABASE ..MODIFY FILE/Make database ONLINE 

    Alter Database Modify File example - 

       NAME ='TestDB_logfile'
       ,FILENAME = 'S:\MSSQL\Data\TestDB.LDF'

    Anup | Database Consultant

    Blog: Twitter: Follow me !

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    martes, 13 de marzo de 2012 18:42
  • I was hoping I could do a filegroup backup of the primary filegroup, but restore only one file from ti to a new location.
    miércoles, 14 de marzo de 2012 16:42
  • You can restore only one file, but the database will be unusable. Also, you can't change the layout (except for physical filename) of a database using backup/restore. For instance, you can't consolidate two files into one using backup/restore.

    Why do you want to move the mdf file to a different drive and add another secondary file where the mdf file used to be? Why not just add that third file to the new location instead (and leave the mdf file where it is)?

    In case you want to move a file for a database, type below in Books Online index, it has detailed instructions on how to move a database file:

    move database

    Tibor Karaszi, SQL Server MVP | web | blog

    miércoles, 14 de marzo de 2012 17:06
  • Well...the whole problem is running out of space.  I originally just wanted to create a new datafile on a drive we have space on G:\.  However the application team thinks creating a new datafile on drive G:\ will cause write contention with the other files on G:\.

    So they came up with an idea, to move the file that is iwritten to the least 'filea.mdf' to the G:\ drive to avoid contention and to create a new datafile where filea.mdf used to be drive h:\.

    THe file is 477G, I was trying to move it the fastest possible way.

    miércoles, 14 de marzo de 2012 19:08
  • Hey there!  This may be a bit off topic, but have a look at this:

    That tool will help you manage your used space better.  The treemap represents each file as a colored rectangle, the area of which is proportional to the file's size.  You can see which clusters are more and less filled with data; all over your harddrive.  Maybe it can help you figure out where you have extremely large data files.

    viernes, 23 de marzo de 2012 12:02