Shrink and Backup

  • If i have a database of 500gb and i would get 200gb space back by shrinking as i have done some updates and deletes. But i want to backup the database and restore without shrinking so does the empty space also be backed up or just the data?

  • Just the data. You could do a quick test by creating a database with initial file size at 500MB and then back it up to see the size.

  • the size of the backup will be equivalent to the space used within the database. But the database also contains information on the sizes of the files that make up the backed up database. When the backup is restored the files will be created to the same size as the original database.

    there is no way round this using native SQL commands but I believe third party tools such as hyperbac can get round this

    ---------------------------------------------------------------------

  • Keep in mind that shrinking will also fragment indexes, so after you shrank, you'd want to rebuild indexes, so allow enough free space for that.

    Hyperbac technology, now Virtual Restore from Red Gate, can "mount" the backup file for use without actually using the disk space.

  • The backup size will be 300GB + Required Transaction log + DBInformation(approx). It will not include the 200GB free space in the database files.

    However, when you restore the backup, the files will be the same size they were when the backup was taken.

  • Tara-1044200 (8/17/2010)


    If i have a database of 500gb and i would get 200gb space back by shrinking as i have done some updates and deletes.

    what was the size of backup file there ? i am sure it would be around 300gb

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Looks like it didnt backup the empty space of around 200gb but when i restored again oin different server even the empty space was restored. So i am shrinking on the server i just restored.

  • Restores do build files the same size as the original. That is a problem for some people, and a shrink is the way to handle this if the data will not need that space. Be aware, that shrinking will likely induce fragmentation, so you might want to leave enough space to rebuild indexes.

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

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