Why yes I can do table level restores

Table level restores are available in SQL Server 2008 R2 Standard…under the right circumstances.

Are you under these circumstances?

For better or for worse you probably aren’t under those circumstances so sorry or good for you, take your pick. MS Dynamics NAV running on SQL Server has the feature to do a restore of a subset of tables without having to restore the entire database. This is especially great when your database has 160 thousand plus tables and you only need to restore some of them. My implementation of Dynamics NAV has the the database split up in to companies and each company has 1,600+ tables. End users come to me with requsts to recover data in a company or refresh a testing environment with the lasted information from production fairly often. I could restore production to a new location and script out the 1,600 tables, but frankly that sounds like a pain. Luckly for me Dynamics NAV has the option to backup and restore by company. I can delete and restore those 1,600+ tables with a few mouse clicks and a fraction of the time a full restore would take.

While connected to your backup target with Classic Client, select Tools > Backup. Select the Company you want to backup.

Are you absolutely sure? Cause it is going to ask you.

Connect to your restore target, select File > Company > Delete. Click Yes an appropriate number of times when you are really, really sure this is in fact the company you want to delete.

Are you absolutely sure? Cause it is going to ask you.

Click again on Tools > Restore. Browse to your backup and so on and so forth. Much faster than the full restore.

My second method of table level restores takes a little more of a custom setup. Sometimes when you are in a rush to get an application into production the application periodically and undesirably deletes all of the data in a table. You’ve all had this problem, right? I didn’t feel like restoring the whole database just to get back a few hundred rows. I also didn’t have the budget/authority/etc to get a sweet 3rd party backup solution. So here comes the never been seen before combination of an INSERT script and SQL Agent.

Bill, “an INSERT script?” Ted, “and SQL Agent?” B&T, “Whoa”

In a utility database create a table with all the fields for your at risk table, plus in my case a company identifier, and a datestamp column with a GETDATE() default. This is where right clicking in SSMS on the table and selecting Script Table as > CREATE To comes in really handy. Name it something catchy like dbo.WhyInTheHellAmIConstantlyLossingDataFromThisStupidTable or if you’re not that creative just use a similar name as your problem table(s).


INSERT INTO [SQLAdministration].[dbo].[WhyInTheHellAmIConstantlyLossingDataFromThisStupidTable]
(company or other customer field
, all other fields from your problem table)
SELECT 'Company$ProblemTable' AS Company
, all of the fields in this table
FROM [ProductionDatabase].[dbo].[Company$ProblemTable]


Then set up a job to load the full contents of that table from production every X time units. Mine is running every 10 minutes. Don’t forget to include a trim step in your job. You don’t want these rows piling up forever. Or maybe you do. I don’t know. Maybe you like to impress others with the size of your tables.

Now when users coming running to you like their data is on fire (remember deleted data is not the same as burning data, trust me on this one) you can smoothly tell them that you can get their data back and save the day like the DBA superhero you are. That cape is a good look for you. Tights? Maybe, maybe not.

#SQLNewBlogger

Thanks again Ed Leighton-Dick (T|B)