August 3, 2011 at 9:52 am
Hi, I hope this is the right category to discuss this. We currently use a 3rd party vendor to backup and restore our databases. The only thing it does not do is give us the ability to restore a single table from within a full backup. We have huge databases in our environment and sometimes it takes a couple of hours to restore the whole database when we just need to restore a table or two.
What solutions do other DBA admins use for this type of situation, do you just restore the whole DB or use a product to restore a table. If there is a norm on doing things, what is the best way.
August 3, 2011 at 10:00 am
In the event of needing a single table restored there are not many solutions for it.
I am not sure there is a product available to do that.
Secondly, if you are not using multiple filegroups, filegroup backups you limit yourself (natively) to just a full database restore.
That said, if there are changes/updates to a specific table, I like to copy that table into a "new" table for the very purpose of restore in the event of failure.
It really boils down to planning for this type of recovery and the steps you take before this type of recovery is required.
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
August 3, 2011 at 11:35 am
Have a look at redgate's "SQL Virtual Restore". Seems to be exactly what you're looking for.
August 3, 2011 at 12:36 pm
Thanks for the response. I been looking at LiteSpeed from Quest. It has Object level Recovery but I'm sure this is pricey.
August 3, 2011 at 3:02 pm
Idera also has object level restore. I don't think any of the 3rd party backup tools are very expensive.
August 3, 2011 at 3:54 pm
danymorales17 (8/3/2011)
Thanks for the response. I been looking at LiteSpeed from Quest. It has Object level Recovery but I'm sure this is pricey.
Be aware that this is only available in the professional version - which is going to cost more. The standard version will run you at least $1000 per server/cluster - with the professional costing quite a bit more.
Don't quote me on the cost - it really depends on your contract with Quest.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2011 at 9:53 pm
danymorales17 (8/3/2011)
Hi, I hope this is the right category to discuss this. We currently use a 3rd party vendor to backup and restore our databases. The only thing it does not do is give us the ability to restore a single table from within a full backup. We have huge databases in our environment and sometimes it takes a couple of hours to restore the whole database when we just need to restore a table or two.What solutions do other DBA admins use for this type of situation, do you just restore the whole DB or use a product to restore a table. If there is a norm on doing things, what is the best way.
The "best" way is to have people stop making mistakes in that database. That normally means "locking down" access to the database. Yeah... I know... tough thing to do.
If such a thing (needing to restore a table) happens a lot, I think the easiest way to "restore individual tables" has already been stated. Buy (or have the 3rd party buy) the backup software (there are a couple, as previously stated) that's capable of restoring a single table. Yep... you'll pay for it but it's better than having a bunch of $20-$60 an hour people waiting around. A thousand to a couple of thousand bucks for such software doesn't sound so bad if you put it in that light.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2011 at 4:14 am
You should be aware that some 3rd party tools that are able to recover individual tables from a backup set may give you different data than if the entire database had been restored, and the data extracted from the table. See here for details.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 4, 2011 at 4:48 am
I see someone else mentioned it, Red Gate's SQL Virtual Restore allows you to "restore" a database backup. You can even restore log files to a point in time, but you do it all to a virtual location. With that in place, it's a snap to then retrieve an object from it. I wrote an article on how to use it here[/url].
Also, just so you know, SQL Compare & SQL Data Compare can be hooked to a backup file. If you just need to recover from the back and you don't need to use the log files, those tools will work for you and they're priced to move.
Full disclosure, I'm the Red Gate booth babe.
"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
August 4, 2011 at 5:32 am
Grant Fritchey (8/4/2011)
I see someone else mentioned it, Red Gate's SQL Virtual Restore allows you to "restore" a database backup. You can even restore log files to a point in time, but you do it all to a virtual location. With that in place, it's a snap to then retrieve an object from it. I wrote an article on how to use it here[/url].Also, just so you know, SQL Compare & SQL Data Compare can be hooked to a backup file. If you just need to recover from the back and you don't need to use the log files, those tools will work for you and they're priced to move.
Full disclosure, I'm the Red Gate booth babe.
Babe might be pushing it. I'd rather not see you in a bikini, again. :w00t:
August 4, 2011 at 5:39 am
I have had to do this kind of activity in the past at which point I restored the entire DB on a different box and queried out the data from the table and then synced them up. Mainly cause as the the data needed to be verfied by the developers before we could confirm if it needed to be restored.
Frankly I think I would prefer this approach over a tool , coz i cant imagine having to do this frequently and so cant justify the cost.
August 4, 2011 at 5:48 am
Thanks for the replies all, first time at forums didn't know what replies I would get.
Let me ask you all this, what about Developer/QA servers. What if you have a database that is over 100 gigs and a developer needs an object restored or QA, how do you guys handle that? This is where I was mostly getting at. Right now we are currently developing and QAing on the same single database but I would like to separate these environment and would like an easier way to refresh both without having to back up and restore a 100 gig DB each time.
Red-gate does have virtual restore but it is a separate product from their compression restore product. The same goes for Idera, you need to products in order to accomplish this. This is why I mentioned SQL LiteSpeed as it has it in 1 single product. Does anyone else know any other all-in-one products or are those 3 the major vendors right now?
Thanks
August 4, 2011 at 5:52 am
danymorales17 (8/4/2011)
Thanks for the replies all, first time at forums didn't know what replies I would get.Let me ask you all this, what about Developer/QA servers. What if you have a database that is over 100 gigs and a developer needs an object restored or QA, how do you guys handle that? This is where I was mostly getting at. Right now we are currently developing and QAing on the same single database but I would like to separate these environment and would like an easier way to refresh both without having to back up and restore a 100 gig DB each time.
Red-gate does have virtual restore but it is a separate product from their compression restore product. The same goes for Idera, you need to products in order to accomplish this. This is why I mentioned SQL LiteSpeed as it has it in 1 single product. Does anyone else know any other all-in-one products or are those 3 the major vendors right now?
Thanks
No, if you picked up Virtual Restore, you wouldn't have to buy another product. It's separate, stands & operates alone. It's completely different from the backup technologies, so it's sold as a separate object. The beauty of it is, restoring a 100gb database using Virtual Restore would only use a few mb (depending on the number of rollback & rollforward transactions in the backup). Then you can treat that like a regular database, edit data, etc., until the next backup. What's more, you can actually use a single backup on multiple servers, saving even more disk space.
Sorry, don't want to sound like a salesman, but I really love that little piece of technology.
"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
August 4, 2011 at 6:01 am
You are right but I still need a product for normal backups with compression and encryption. In a couple of weeks I will try both products out. Looks like Redgate gives you a discount for bulk purchase and their bundles. Thanks!
August 4, 2011 at 6:36 am
dbdmora (8/4/2011)
What if you have a database that is over 100 gigs and a developer needs an object restored or QA, how do you guys handle that?
What we used to do in my previous company was to set up a daily restore job to restore the latest backup of the production database (around 400 GB), and scrub/obfuscate the sensitive data. This served 2 purposes: it ensured that the backup set was recoverable, and served as a test database for developers/QAs. In our case, it was very unlikely that only a single object was required, as most tables are linked in some way or another. Extracting only a single table would usually result in inconsistent data in the database.
These days, you have products like SQL Virtual Restore, that allow you to mount fully live databases from a backup set. Your developers/QAs can work off those mounted databases directly. You gain in space and time savings compared to actually restoring the database.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply