Restoring a database and simultaneously increasing it's file size

  • Hey guys,

    At my organization, we have just built a new SQL Server machine to which we would like to migrate existing databases from other servers. For one of the databases, we'd like the MDF and LDF to have a new initial file size once it is moved. What is the best way to accomplish this? Is there a way to increase the file sizes during restoration from a backup? Or will we have to restore and than alter the file size through T-SQL (or SSMS)?

    The reason we are asking, is because we want to ensure that the MDF and LDF files on the new server are contiguous in the file system, and we are worried that increasing the file size after restoration will lead to having non-contiguous files in the file system.

    If there is more detail I can provide, just let me know.

    Thanks,

  • A restore will recreate the database as it was at the time of backup. If you want something different, do the restore then make the changes.

    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
  • GilaMonster (8/14/2013)


    A restore will recreate the database as it was at the time of backup. If you want something different, do the restore then make the changes.

    Well, if we increase the MDF and LDF file sizes after the restore, will the files be contiguous on the file system?

  • Maybe, maybe not. Depends on way too many factors to answer.

    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
  • GilaMonster (8/14/2013)


    Maybe, maybe not. Depends on way too many factors to answer.

    Ah, okay, I was afraid so. How does this sound then?

    1) Restore the Database on the new machine

    2) Increase the filesize

    3) Backup the new database

    4) Restore the backup of the increased database

    In your opinion would that help ensure that the database file's are contiguous?

  • Maybe. Why's it so essential that the files be contiguous?

    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
  • GilaMonster (8/14/2013)


    Maybe. Why's it so essential that the files be contiguous?

    Being a brand new server, we'd like to start it off on the right foot. Based on this MSDN article:

    http://technet.microsoft.com/en-us/library/ms188917.aspx

    Extent Fragmentation

    This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

    We'd like to start with extent fragmentation as close to zero as possible.

  • Extent fragmentation has to do with the position of extents within the file, not the file in the storage system. No amount of backup/restore will change the position of extents within a data file.

    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
  • dgutie02 (8/14/2013)


    Ah, okay, I was afraid so. How does this sound then?

    1) Restore the Database on the new machine

    2) Increase the filesize

    3) Backup the new database

    4) Restore the backup of the increased database

    I am not sure that this will help. It is not unlikely that you will get back the same disk blocks.

    You could extend the files on the old server before you backup.

    Else, stop SQL Server and let loose the disk fragementer of your choice.

    And I agree with you: database files should of course of be contiguous on disk.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • If you buy the right disk defragmentation system, you can defragment SQL Server MDF, LDF, and NDF files while the system is up and running with little or no degredation in performance while it's defragging. Here's one that I used many years ago (disclaimer... I've not tested this particular version). It was a beautiful thing because once I set it up, I never had to touch it again.

    http://www.condusiv.com/solutions/server-solutions/sql-server/default.aspx

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/14/2013)


    If you buy the right disk defragmentation system, you can defragment SQL Server MDF, LDF, and NDF files while the system is up and running with little or no degredation in performance while it's defragging.

    You can, but to be honest I'd usually recommend that SQL be shut down for a disk defrag, as if there's any problems with that online defrag tool, the DB could end up corrupt or worse.

    Also worth noting that SANs usually have their own tools for defragging, SSDs probably shouldn't be defragged at all (check the specific SSD's manual and guidelines) and some SAN designs intentionally fragment files in order to spread the IO out across as many disks as possible to improve read throughput.

    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

Viewing 11 posts - 1 through 10 (of 10 total)

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