0

I am reading the book 'Pro SQL Server 2019 Administration' from Carter:

enter image description here

In a scenario in which a user accidentaly deletes data; he mentions that we can recover the data (from the DR server) and then "repopulate the production server".

Then he adds "This means that you do not need to restore a backup to recover the data."

What he means by 'repopulate'? and how is this better/different than a backup/restore?

Chicago1988
  • 261
  • 1
  • 8

2 Answers2

1

Because the database at the DR site is in read_only mode, you can export the data from the DR table where the deletes occurred on the primary and 'repopulate' the primary server for just that one table using the data exported from the DR site. This would be especially useful if the database is extremely large, because you would not have to restore the entire database to get just that one table recovered.

This, of course, assumes the log records containing the deletes have not been applied to the DR database.

Scott Hodgin - Retired
  • 23,854
  • 2
  • 26
  • 48
1

For the sake of discussion, let's assume that there is a 30 minute delay (lag) in applying changes to the database on the DR server.

  • 10:00 User deletes something they shouldn't have.
  • 10:10 They call you.
  • 10:20 You query the (as yet unchanged table) on the standby. You find the rows that they deleted and copy them into another table, port the data back to the primary database and re-insert them.
  • 10:30 The log shipping "catches up" and the rows are deleted from the standby.
  • 10:50 Thirty minutes after you put them "back" onto the primary, the rows get re-added to the standby.

All you've done is query a table into another table, ported that to another database and run another insert..select to re-populate the rows into the original table.

Let's face it; that's easy.

The alternative?

You would have to recover a copy of the database from a Point-in-Time [just] before they deleted the rows. Depending on the size of the database, that could take a while and, of course, you have to have the disk space on hand to do the recovery (you should have this on "hot-standby", just for emergencies).

Then you can do the whole insert..select, port, insert..select thing again.

That sounds "nice", but ... quick Reality Check.

Thirty minutes is an Eternity in computing time. Having that much lag in your DR "loop" is just asking for trouble (although your Recovery Objectives might say different). Seconds, rather than minutes, is usually the order of business here. By the time the User has realised they've done something dumb, you'd normally expect the standby to be in the same, messed-up state as the primary.

Phill W.
  • 8,706
  • 1
  • 11
  • 21