January 7, 2011 at 12:31 pm
I was told that both types of restore require the same space requirements. Is this true? If I have a 400 gig database do I need another 400 gigs of free space to do a point in time recovery?
Don
January 7, 2011 at 1:24 pm
Donalith (1/7/2011)
I was told that both types of restore require the same space requirements. Is this true? If I have a 400 gig database do I need another 400 gigs of free space to do a point in time recovery?Don
Yes, if not more. It depends on what happened in the logs after the source backup. If someone added in a ton of data after the primary backup that you're restoring from, yep, you'll need more room.
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
January 7, 2011 at 1:42 pm
Craig Farrell (1/7/2011)
Donalith (1/7/2011)
I was told that both types of restore require the same space requirements. Is this true? If I have a 400 gig database do I need another 400 gigs of free space to do a point in time recovery?Don
Yes, if not more. It depends on what happened in the logs after the source backup. If someone added in a ton of data after the primary backup that you're restoring from, yep, you'll need more room.
I'm not so sure about that one. We have a database that is a couple of hundred gigs and probably has logs of about 75-100 gigs a day, but the database size is still just a couple of hundred gigs. This is because the logs aren't just insterts it's also deletes & updates, so there isn't a hard & fast rule in this area. You kind of have to know what happened between the last full backup and the moment in time you're recovering to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2011 at 2:00 pm
Grant Fritchey (1/7/2011)
Craig Farrell (1/7/2011)
Donalith (1/7/2011)
I was told that both types of restore require the same space requirements. Is this true? If I have a 400 gig database do I need another 400 gigs of free space to do a point in time recovery?Don
Yes, if not more. It depends on what happened in the logs after the source backup. If someone added in a ton of data after the primary backup that you're restoring from, yep, you'll need more room.
I'm not so sure about that one. We have a database that is a couple of hundred gigs and probably has logs of about 75-100 gigs a day, but the database size is still just a couple of hundred gigs. This is because the logs aren't just insterts it's also deletes & updates, so there isn't a hard & fast rule in this area. You kind of have to know what happened between the last full backup and the moment in time you're recovering to.
You're right of course. I was just trying to prep him for a worst case scenario but also answer his question... and I was being a bit lazy about all scenarios.
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
January 7, 2011 at 2:36 pm
Donalith (1/7/2011)
I was told that both types of restore require the same space requirements. Is this true? If I have a 400 gig database do I need another 400 gigs of free space to do a point in time recovery?
Bear in mind that point-in-time restore means restoring the full backup, restoring differential backup if applicable, then restoring all log backup in order up to the point in time that you want to stop at.
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
January 7, 2011 at 2:44 pm
Ok, thanks everyone. It was an accidental conditional delete on some 26 rows on a relatively inactive table. It made more sense, I guess, to do a full restore to another temporary database, move the missing data, and then just drop that database again.
January 8, 2011 at 2:31 pm
Donalith (1/7/2011)
Ok, thanks everyone. It was an accidental conditional delete on some 26 rows on a relatively inactive table. It made more sense, I guess, to do a full restore to another temporary database, move the missing data, and then just drop that database again.
If you have a product such as Litespeed or SQLBackup you are able to script out the insert statements for the missing data and re apply them to the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 9, 2011 at 7:34 am
There's also Red Gate SQL Virtual Restore, that would allow you to "restore" the database to a different location and pull the data off. The nice thing about this is, it doesn't use any disk space at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2011 at 3:54 pm
It doesn't use any disk space? Is it faster or slower than a regular, disk-using, restore?
January 11, 2011 at 5:44 am
Donalith (1/10/2011)
It doesn't use any disk space? Is it faster or slower than a regular, disk-using, restore?
Restore time is about the same as the regular restore because most of that time is SQL Server walking the structure to validate it as it puts it back together. But no, it doesn't use disk space. It spoofs SQL Server into thinking the OS has oodles of space available. It just doesn't use it.
The one test I have not yet run, but plan to, is hooking multiple servers to a single backup to see what happens. So, no comment on if that's good or bad. I do know it's possible, I just don't know what happens.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply