Post reply

If i partition my mdf file

  • August 29, 2011 at 10:24 am

    #144300

    into yearly NDF files that result in four NDFs, do i wind up with five backup files?

    I need to make a big MDF smaller. I have a database that is almost 100 GB, so backing it up and/or moving it is a chore.

    I want to partition the database into yearly partitions, but before i do that i want to know if the operation will wind up with one MDF and four NDFS, and five BAK files approx 20 GB each, so if we have to move them, we can move five 20s instead of one 100 GB?

    thanks a lot

    drew

  • August 29, 2011 at 2:56 pm

    #1374740

    A regular database backup consists of ALL the files in the database, so you'd have one 100GB database backup. You can do FILE or FILEGROUP backups to break up a backup.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 29, 2011 at 9:19 pm

    #1374802

    so i think that means file groups, because i dont think i get to control how the data is distributed to just another NDF, but the partition scheme maps partitions to file groups, so that's the unit (file group) i need to control the size of the backup, assuming years have equal volume, which of course, they do not ( but i cant justify multiplying the partition expansion by 12 going to monthly chunks).

    thanks very much for your help and explanation

    drew

  • August 30, 2011 at 4:16 am

    #1374873

    Read up on piecemeal restores before you go that way. You don't want to be left in a state where you can't restore the DB fully.

    Basically, unless the filegroups are read-only, you need to be in full or bulk-logged recovery and have log backups covering the interval between the oldest backup and newest to restore the DB entirely.

    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

  • August 30, 2011 at 11:38 am

    #1375165

    i have had a charmed life because everything has been in one file all being backed up and restored by CommVault. Very few systems are on full backup with log backup every fifteen minutes, so that's going to be another wild card in this excursion...

    thanks very much for the advice, i will read about piecemeal restores before i do anything.

    drew

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

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