August 17, 2010 at 2:51 pm
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?
August 17, 2010 at 3:07 pm
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.
August 17, 2010 at 3:14 pm
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
---------------------------------------------------------------------
August 17, 2010 at 9:51 pm
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.
August 17, 2010 at 10:18 pm
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.
August 17, 2010 at 11:24 pm
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;-)
August 18, 2010 at 7:40 am
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.
August 18, 2010 at 7:43 am
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