Question

grmihel on Tue, 22 Jan 2013 10:43:17


I have scheduled a backup plan for my MSSQL Express as following:

- A Full backup every day (before the working hour start)

- One Differential backup every hour

- One Transaction Log backup every 5 minuts

It seems to work quiet well, and the backup files ain't getting too big, as if  I did a diff backup every 5 minuts. But I'm not sure yet how the coverage of the backup is, and wanted to hear your experience before I actually need the backup for restore purpose.

Lets say that within 2 differential backups, there is 11 Transaction Log backups:

  1. Differential (10:00am)
  2. Transactions log (10:05am)
  3. Transactions log (10:05am)
  4. Transactions log (10:10am)
  5. Transactions log (10:15am)
  6. Transactions log (10:20am)
  7. Transactions log (10:25am)
  8. Transactions log (10:30am)
  9. Transactions log (10:35am)
  10. Transactions log (10:40am)
  11. Transactions log (10:45am)
  12. Transactions log (10:50am)
  13. Transactions log (10:55am)
  14. Differential (11:00am)

Lets say thats there was deleted a row at 10:12am, and the loss was first dicovered at 10:40am. Now I want to restore the backup before the deleting, with the backup taken at 10:10am (right before the delete action), but is it possible to restore at the point before the delete action, and in the same time keep the new data changes that have been created between that target restore point and until current working time?? And how does it work?


Sponsored



Replies

Stan210 on Tue, 22 Jan 2013 11:05:59


No,It is not possible to restore this way but you can restore the database as something else and copy the data from restored database to new database or if you are using any 3rd party tool(LiteSpeed or RedGate), you can read the log and extract the information. All the Log backups are needed to bring database to one consistent point, so we cannot skip one log backup and the restore the next one. 

Hope it Helps!!



Sean Gallardy on Tue, 22 Jan 2013 14:53:48


Hello,

The best thing to do when putting together a proper backup plan is to first put together a solid and reliable restore plan that takes into account any SLAs, RPO, and RTO for the databases in question.

What does this mean for you? It means first, start with what you require. Do you require up to the second coverage? How much data loss is acceptable? How much time do you have to recover in the event of a disaster? How long does it take to get the backups from tape/disk/remote site?

Once the requirements for the restore are in order, a backup plan can be tested to make sure it fits inside of the restore requirements. For example, In your current scenario with a differential every hour and transaction log backups every 5 minutes, the worst case is a disaster at 10:59 AM. Your recovery line is as follows:

1. Most recent FULL database backup (Not sure when this occurs in your time frame?)
2. Most recent differential (10 AM)
3. Every transaction log from roughly 10AM until 10:59 tail of the log backup (if possible). If the tail of the log is not possible (say a disk/san crash) then 4 minutes of data have been lost.

The above restore scenario is roughly 1 full + 1 differential + 19/20 transaction logs = 21/22 restore commands. Transaction logs must be replayed and thus aren't a straight copy over as full and differentials are. How long is this taking to run in your environment? How easy and fast is it to get the full backup? How often are the backup files transferred to another storage device or medium to protect from corruption or device loss?

If your scenario about the row deleted - you'll need to do everything above except use the STOPAT clause and STANDBY of the RESTORE command to stop at arbitrary times and find the time right before the row is deleted. If using STANDBY, copy the row out and into the production database. Issue resolved.

These are just some of the things to be aware of when putting together a solid backup plan. You may find that restore 20 transaction log backups requires too much time or that it takes 2 hours to retreive a full backup from an offsite tape (I don't know your infrastructure, just speculation) and that might not be acceptable with management or business partners.

If you can meet all of your requirements then the plan that you have is good to go. If not, try to revise it to meet the current and future growth of the business.

-Sean


Sean Gallardy | Blog | Twitter


grmihel on Tue, 22 Jan 2013 15:16:45


No,It is not possible to restore this way but you can restore the database as something else and copy the data from restored database to new database or if you are using any 3rd party tool(LiteSpeed or RedGate), you can read the log and extract the information. All the Log backups are needed to bring database to one consistent point, so we cannot skip one log backup and the restore the next one. 

Hope it Helps!!



The only problem doing it by copy from one to another manually, is that the database is quiet complex, meaning there is a lot of bindings with FKs/PKs.

So if I loose some data, but not all of it, its either to find the old data and enter once again, or screw the current data between the two points, and create a restore from the loss time??

Sean Gallardy on Tue, 22 Jan 2013 15:41:52


Hello,

The only problem doing it by copy from one to another manually, is that the database is quiet complex, meaning there is a lot of bindings with FKs/PKs. So if I loose some data, but not all of it, its either to find the old data and enter once again, or screw the current data between the two points, and create a restore from the loss time??

Welcome to the joys of being a DBA. You could attempt ot call the company if this is a 3rd party database and ask what their recovery support is and if tey have any pre-made scripts or solutions for this. If not or if it's internal, good luck.

-Sean

Stan210 on Wed, 23 Jan 2013 00:13:29


Unfortunately, we cannot get the lost data and current data. we can either restore to the point before delete operation and script out the missing data and insert it back to the  original table, this would be much easier if you know, what range of values were deleted and, if deletions happened on tables that does not have too many delete cascade constraints...  One other thins, you can download LiteSpeed tool(trail version) and read the Transaction log back where the deletion happened and oit will give the rows, that were deleted during the time and you can copy them...I hope, when you say "manually" you were referring to manual scripting not manual data entry...Scripts will do that for you..Anyways, I agree it is kinda pain... 

Kumar muppa on Wed, 23 Jan 2013 06:37:41


Hi there,

you can do it but not with existing Microsoft tools. Easy way would be if you know what was deleted simply insert it.

Or restore as a separate database and compare both so that you can figure out the differences. Again you have to use third party tools.

thanks

kumar

Fanny Liu on Wed, 23 Jan 2013 06:58:31


Hello,

If you know when the mistake happened, you just use that time (a few moments before) for the STOPAT parameter in the final RESTORE LOG command. If you don't know the time, you can wan read the log backup when the accident happened by using some third party tools. 
For example:  ApexSQL: ApexSQL Log  

Redgate: SQL Log Rescue

Regards,
Fanny Liu