May 3, 2011 at 9:57 am
Hi,
I'm trying to do a restore and my backup file is 453GB and the disk I'm trying to restore to has 478GB free space. the actual database that I took the backup properties shows the database size as 525GB, which is including free space avail. Here is the actual error:
Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'I:\' to create the database. The database requires 520161263616 additional free bytes, while only 514240606208 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
So am i reading that correct in that I need 520GB of disk space to restore? Or I need at least a min of 514GB of free disk space to restore? I'm just trying to make sure I give the sysadmin an accurate number.
THanks!
Isabelle
Thanks!
Bea Isabelle
May 3, 2011 at 10:02 am
Yes you need 520 GB.
The restore makes a perfect copy of the backed up DB. File sizes included
May 3, 2011 at 10:07 am
Echoing what Ninja said. You need to have sufficient space (>= size of db to be restored) on a volume to restore a database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 10:10 am
ok...thank you both for that quick confirmation! That's what I was thinking but just wanted to be sure.
😀
Isabelle
Thanks!
Bea Isabelle
May 3, 2011 at 10:16 am
As for the solution you can get a drive with 520+ GB free space.
Or you could restore that db on another server, put in simple, checkpoint, shrink the log to 0 (or a couple 100mbs).
retake full backup. copy / restore.
The only problem with this solution is that you still need a server than can do a restore so it's the chicken and the egg problem... and that it,ll take you 3-5 times the amount of time it would if you had the space in the first place.
May 3, 2011 at 10:27 am
One thing to think about is keeping a 1TB USB drive around for times like this. Make the restore happen on that volume, then try to resize the database.
Another alternative that can help is Virtual Restore, from Red Gate (my employer) that can mount the backup file and save you a tremendous amount of space.
May 3, 2011 at 10:28 am
Steve Jones - SSC Editor (5/3/2011)
One thing to think about is keeping a 1TB USB drive around for times like this. Make the restore happen on that volume, then try to resize the database.Another alternative that can help is Virtual Restore, from Red Gate (my employer) that can mount the backup file and save you a tremendous amount of space.
How long to restore on such a drive?
May 3, 2011 at 10:32 am
I would love to do that for our test environment where space is a premium...restore it and then resize before moving it to the server. Great suggestion!
Thanks,
Isabelle
Thanks!
Bea Isabelle
May 3, 2011 at 10:37 am
Then you might want to look into replication. Maybe it's 1 way to solve the problem (assuming maybe a BI environement and NOT full OLTP.)
May 3, 2011 at 10:51 am
The time to restore on a USB drive will vary (SB2, USB3 interface). eSata will be quicker, but it's time.
For Virtual Restore it's very quick. No data is copied and minimal sparse files are created on your drive.
May 3, 2011 at 10:55 am
Steve Jones - SSC Editor (5/3/2011)
The time to restore on a USB drive will vary (SB2, USB3 interface). eSata will be quicker, but it's time.For Virtual Restore it's very quick. No data is copied and minimal sparse files are created on your drive.
Can it corrupt the backup? I'm sure they're taking every precautions but that's the first objection that immediatly pops in my mind.
I spend almost 1 Hour / day validating that I can use my backups. I'd hate to have that work wasted.
May 3, 2011 at 11:06 am
Nothing is ever written to the backup file. It is read-only. The sparse files created will store any changes made.
May 3, 2011 at 12:18 pm
Steve Jones - SSC Editor (5/3/2011)
The time to restore on a USB drive will vary (SB2, USB3 interface). eSata will be quicker, but it's time.For Virtual Restore it's very quick. No data is copied and minimal sparse files are created on your drive.
Steve, I've found that 99% of servers have their USBs locked down at the server side, if you can even access the data-center these days as a DBA. Is that not the case where you're at?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 3, 2011 at 4:24 pm
Craig Farrell (5/3/2011)
Steve, I've found that 99% of servers have their USBs locked down at the server side, if you can even access the data-center these days as a DBA. Is that not the case where you're at?
Craig,
I think that in most data centers, and most industries, sysadmins don't lock things down. At SQLServerCentral/Red Gate we have a small setup, but I have a few friends at Fortune 1000 companies and they typically don't lock down USB.
However for the OP, I wasn't thinking you needed to restore on the production server. You can grab the backup file and restore on any old developer workstation, then re-size the files, then run a new backup and restore on the production system.
And always, always, delete the restored database, the mdf/ldfs, the backup files and any data that should be secured from the USB drive and any other machines.
May 4, 2011 at 12:49 am
Steve Jones - SSC Editor (5/3/2011)
Craig Farrell (5/3/2011)
Steve, I've found that 99% of servers have their USBs locked down at the server side, if you can even access the data-center these days as a DBA. Is that not the case where you're at?Craig,
I think that in most data centers, and most industries, sysadmins don't lock things down. At SQLServerCentral/Red Gate we have a small setup, but I have a few friends at Fortune 1000 companies and they typically don't lock down USB.
I've been bouncing around healthcare and finance too long, apparently. I forget how easy going things can be... 😀
However for the OP, I wasn't thinking you needed to restore on the production server. You can grab the backup file and restore on any old developer workstation, then re-size the files, then run a new backup and restore on the production system.
And always, always, delete the restored database, the mdf/ldfs, the backup files and any data that should be secured from the USB drive and any other machines.
Hm, I see where you're going in that case. I'll have to rethink a few things, thank you. The idea of production data on a workstation is bad juju to me, in all sorts of ways. I think my last couple of years of experience has made me data-paranoid.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply