Multiple ndf file back up solution please

  • seanthomas2010

    SSC Enthusiast

    Points: 183

    Good morning

    I have a databases at 1.15TB but the Log file is 184GB. So i need space for 966GB

    1MDF file and 28 NDF files.

    I have to backup using a SQL script but i want to split the load of the backups over a few drives. I have one drive which is 560GB free, 310GB and one at 120GB

    with 28ndf files. i want to specifically say which nfd files go one to which drive. I need to backup the database

    The script i am using is. Taken out names for security purposes. Its a Mirrored databases Asynchronous. So need sot be Copy only.

    BACKUP DATABASE DB TO DISK ='L:\Backup\\_1.bak'

    , DISK ='L:\Backup\\.bak'

    , DISK ='L:\Backup\\3.bak'

    , DISK ='L:\Backup\\4.bak'

    , DISK ='L:\Backup\\5.bak'

    , DISK ='L:\Backup\\6.bak'

    , DISK ='L:\Backup\\7.bak'

    , DISK ='L:\Backup\\8.bak'

    , DISK ='L:\Backup\\9.bak'

    , DISK ='L:\Backup\\10.bak'

    , DISK ='L:\Backup\\11.bak'

    , DISK ='L:\Backup\\12.bak'

    , DISK ='L:\Backup\\13.bak'

    , DISK ='L:\Backup\\14.bak'

    , DISK ='L:\Backup\\15.bak'

    , DISK ='L:\Backup\\16.bak'

    , DISK ='L:\Backup\\17.bak'

    , DISK ='L:\Backup\\18.bak'

    , DISK ='L:\Backup\\19.bak'

    , DISK ='L:\Backup\\20.bak'

    , DISK ='L:\Backup\\21.bak'

    , DISK ='L:\Backup\\22.bak'

    , DISK ='L:\Backup\\23.bak'

    , DISK ='L:\Backup\\24.bak'

    , DISK ='L:\Backup\\25.bak'

    , DISK ='L:\Backup\\26.bak'

    , DISK ='L:\Backup\\27.bak'

    , DISK ='L:\Backup\\28.bak'

    , DISK ='L:\Backup\\29.bak'

    WITH COPY_ONLY,

    STATS = 1

    many thanks

  • Gail Shaw

    SSC Guru

    Points: 1004424

    When you back up a database, you back up the entire database. How the backup is written doesn't depend on the file/filegroup structure of the DB.

    By striping the backup (as you've done), you'll get the backup file spread evenly across 29 files. It's not one filegroup per backup file. It's the entire DB backed up and spread across the files.

    When you restore, you specify where each file goes.

    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
  • seanthomas2010

    SSC Enthusiast

    Points: 183

    GilaMonster

    Thanks for the help. If i use the backup section on the DB. then do files and filegroups backup process. then Surly this will allow me to do individual backups for each ndf files. this way i can copy to where i like. then i can add it to the external encrypted drive.

    is this a good idea?

    many thanks Sean

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Not a particularly good idea, as the restore process is more complicated when you do file and filegroup backups. You'll need to take log backups as well and restore those or you won't have a usable DB at the end.

    Besides, what does it matter?

    If you want to split the files over drives for space reasons, use the striped backups like you have. You can put multiple stripes on a drive for space reasons.

    For example, if the full backup is 1TB and you stripe over 10 files, then each file will be about 100GB. If you have one drive of 600GB, one of 300 and one of 200, then you put more of the stripes on the first drive and fewer on the other two.

    Oh, and a backup isn't just the data files. There will be some portion of the log in it too, so you must account for that.

    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
  • seanthomas2010

    SSC Enthusiast

    Points: 183

    Ok i shall do that.

    thank you

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

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