Restore Database Size
-
My production datafiles are deliberately large to allow the data to expand and prevent lots of autogrowth events.
Data size = 20GB
File Size = 100GB (80% free space for growth)
If I start a restore of a full BAK file taken of the database, is there any way to get it to restore without the extra space i.e. only take up 20GB ?
I know I can restore it, then shrink it to achieve thi.
But it would be much easier to do in 1 go...
r
-
After looking at the RESTORE DATABASE statement in BOL, I have to say no. You have to restore then shrink the database if you don't want the extra space.
-
No. When a database is restored it is restored to exactly the same state it was when it was backed up. Including size of files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Why do you need to shrink it? Is this for test?
You can shrink it afterwards, and I've heard that Hyperbac supports this restore (less than original size).
-
I can think of one reason. We restore the one of our databases as a previous year database that is read only. We shrink the database prior to making it read only so that it takes less space on the server. Also, only a few people have access to the previous year database, and it isn't used very frequently.
As for HyperBac shrinking the database on a restore, I'll have to look into that (if I have time) as we use HyperBac here at work.
-
Read-only makes perfect sense, and in that case, you do want a smaller restore.
However as disks fill up, performance does suffer, so even with read only, you want some buffer of space, even if it's for tempdb. In that case, I'd restore, then shrink, but you need to reindex if you shrink, so your log will grow, and then you can shrink that.
-
thank you for all of your input.
the reason for shrinking was to get it over to an old instance with inadequate disk space (development server).
The db size is actually much larger than the example i gave (100gb used, 500gb free), i just simplified for example. The principle was the same.
r
-
There really is no good way to do this if you don't have the space at all. You can play some games, perhaps shrinking tempdb, or detaching other dbs and moving the files temporarily until the space is restored.
Honestly these days I'd beg my boss for a 1TB external USB drive, it's cheap, you can do the restore, and then shrink it and move it back to regular storage.
-
During the restoration process, shrinking the database file is not possible. It always retains the values of its original status.
I prefer to shrink the database files before we take the backup as this will save the disk space and time. The cons is that it leads to disk fragmentations. We may have to rebuild the index for better performance. But at times, this method is useful and I follow this with no issues.
Regards
Atul
-
If you shrink the database, you almost assuredly will introduce fragmentation.
If you know you will restore this particular backup, that makes sense, but I would hope this is not a regular practice.
Hyperbac has this feature, I believe, and Red Gate has said they are looking at it.
-
Maybe you can try replication.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply