Restoring a copy of a database at a point in time to another server

Question

Post reply

Restoring a copy of a database at a point in time to another server

MarkP

SSC Veteran

Points: 245
More actions
April 4, 2024 at 9:35 am
Go to Answer
#4407353

DW/ETL Developer here who does some DBA tasks. Quick question out of my normal subset of DBA abilities.
Database A (full recovery) exists on Server 1 with a set of transaction log backups.
We need to restore a copy of Database A as Database B on to Server 2 but Database A must remain as it is.
I've restored .bak files before to make a copy of the database elsewhere but never had to do a point in time restore to a different server.
If I could have had downtime, I'd have probably backed up Database A in situ, restored it back to the timepoint required, taken another .bak and transferred it off then brought it back to present moment. That isn't possible in this scenario, though, as the two databases aren't on the same network and we aren't allowed downtime on Database A.
Thanks in advance for any/all help.
April 4, 2024 at 12:05 pm

This was removed by the editor as SPAM
Ant-Green

SSC Guru

Points: 113445
More actions
April 4, 2024 at 1:12 pm
Answer
#4407447

If you know how to unpick everything and what data to delete from all the tables to ensure the data is as it should be at the point in time you want it, yeah.
Some tables may not have a timestamp column or may have been updated since then and now, so you probably wont know if a value of "A" is right at 8am, vs a value of "B", unless you have auditing on every table.
The best and safest bet is always a backup and roll forward the logs.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

So how do you get the BAK file from the original server?

Are you manually going making a backup, then moving it as part of your process?

Can the original server automatically ship you the BAK and TRN files periodically?

If you can get the BAK and TRN files from the original place, then you can do the restore to point in time easily, you may just need to change how your process works to make the restore happen correctly.

So you take the BAK file from before the point in time, and restore it with NORECOVERY option

Then take all the TRN files and play them in order, again all with NORECOVERY until the last log which you want to restore with RECOVERY or with STANDBY depending how you want to do the next restore.

So I want to restore to day at 09:00, time now 11:07

Last full backup was today 00:00

Logs every 1 hour

Restore full backup from 00:00 with norecovery

Restore transaction log backup from 01:00 with norecovery

Restore transaction log backup from 02:00 with norecovery

Restore transaction log backup from 03:00 with norecovery

Restore transaction log backup from 04:00 with norecovery

Restore transaction log backup from 05:00 with norecovery

Restore transaction log backup from 06:00 with norecovery

Restore transaction log backup from 07:00 with norecovery

Restore transaction log backup from 08:00 with norecovery

Restore transaction log backup from 09:00 with recovery

The database on your DW side would now be as it was at 09:00

But say you wanted to run incremental loads at 09:00 / 12:00 / 15:00 / 18:00

Then you would want to restore the 09:00 with standby.

This allows you to then come and restore the 10:00/11:00 with norecovery WITHOUT having to restore the full and the 1/2/3/4/5/6/7/8/9 logs again.

Standby allows you to append the next lot of transaction log backups to it.

Restore full backup from 00:00 with norecovery

Restore transaction log backup from 01:00 with norecovery

Restore transaction log backup from 02:00 with norecovery

Restore transaction log backup from 03:00 with norecovery

Restore transaction log backup from 04:00 with norecovery

Restore transaction log backup from 05:00 with norecovery

Restore transaction log backup from 06:00 with norecovery

Restore transaction log backup from 07:00 with norecovery

Restore transaction log backup from 08:00 with norecovery

Restore transaction log backup from 09:00 with standby

Noon comes

Restore transaction log backup from 10:00 with norecovery

Restore transaction log backup from 11:00 with norecovery

Restore transaction log backup from 12:00 with standby

3pm comes

Restore transaction log backup from 13:00 with norecovery

Restore transaction log backup from 14:00 with norecovery

Restore transaction log backup from 15:00 with standby

etc

MarkP SSC Veteran Points: 245 More actions · Answer 2

Thanks for the reply Ant, I was looking for the process and you seem to have hinted at it.

Unfortunately we don't have a .bak from before the target to go at - I'm having to figure out how to in effect rewind a copy of the database back from now to last weekend. Are you saying that one option is:

1/ Take a backup of current Database A as a .bak

2/ Copy to Server 2 along with the trn files that exist between now and the target date over last weekend

3/ Restore the database .bak as Database B and then apply the point in time restore using the trn files?

Again, as a DW/ETL Developer, my DBA skills are limited to what I've had to do in the past when there hasn't been a DBA available. Thanks

Ken McKelvey SSCoach Points: 18769 More actions · Answer 3

MarkP wrote:

I'm having to figure out how to in effect rewind a copy of the database back from now to last weekend

This is not possible. You will need a bak file from before last weekend and then restore subsequent trn files up to the point in time you want to recover at.

MarkP SSC Veteran Points: 245 More actions · Answer 4

Ken McKelvey wrote:

MarkP wrote:
I'm having to figure out how to in effect rewind a copy of the database back from now to last weekend
This is not possible. You will need a bak file from before last weekend and then restore subsequent trn files up to the point in time you want to recover at.

I can "rewind" to last Saturday on Database A with the current transaction logs though. So what is the difference? As I said, not a DBA, so trying to understand why I could do it to Database A on Server 1 but not a copy on Server 2.

Ken McKelvey SSCoach Points: 18769 More actions · Answer 5

MarkP wrote:

I can "rewind" to last Saturday on Database A with the current transaction logs though.

I would be interested to see how you do that. I suspect it would require a very detailed knowledge of the internals of SQL Server along will a good knowledge of the application logging/auditing.

This reply was modified 1 month ago by Ken McKelvey.

MarkP SSC Veteran Points: 245 More actions · Answer 6

Let me rephrase what I'm after. I've borrowed the screenshot from a different example but the same principle applies.

I have a database in its current state and the backups below. My requirement is to restore the whole of the first four sets below to take me back to how the database was at 8AM but to have that applied on a copy of the database on a different server. I have access to the database and its backup sets, just wanted to know how to achieve it. Does that make more sense? Can I simply take a backup of the existing database and those transaction logs, copy them to a new server, restore the database then apply the rollback of those transaction logs?

restore_example

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

So based on that picture and to get it to 8am on the 8/4/2022

Restore the 11:00pm full with no recovery

Restore the 6am diff with no recovery

Restore the 6:30/7/7:30 logs with no recovery

Restore the 8am logs with recovery.

MarkP SSC Veteran Points: 245 More actions · Answer 8

Ant-Green wrote:

So based on that picture and to get it to 8am on the 8/4/2022
Restore the 11:00pm full with no recovery Restore the 6am diff with no recovery Restore the 6:30/7/7:30 logs with no recovery Restore the 8am logs with recovery.

Thanks Ant. I'll see if they have any older backups but I suspect that they won't.

They have inherited the database/server and turned backups on at the weekend but only transaction logs and didn't do a full backup.

Is there no way of unravelling from current through the series of trns or are the trns only of use if you have an older .bak to apply them to?

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

TRNs only as good as an older FULL

Without a FULL to start the chain then all subsequent logs are useless.

MarkP SSC Veteran Points: 245 More actions · Answer 10

Ant-Green wrote:

TRNs only as good as an older FULL
Without a FULL to start the chain then all subsequent logs are useless.

Bugger.

OK, so it appears I have two options. Either:

Go back to the other people who had the server last week and see if they have a suitable backup.

Or just take a backup of current and remove all transactions since weekend.

Is that how you see it?

Ken McKelvey SSCoach Points: 18769 More actions · Answer 11

MarkP wrote:

Thanks Ant. I'll see if they have any older backups but I suspect that they won't.

To get a log backup there must have been at least one full backup since the database was put in FULL recovery. If the full back was done with VSS then it may not be any good. Try and get them to run something like the following on the source server:

SELECT TOP (1) M.physical_device_name, S.backup_finish_date
FROM msdb.dbo.backupset S
       JOIN msdb.dbo.backupmediafamily M
              ON S.media_set_id = M.media_set_id
WHERE M.physical_device_name NOT LIKE '{%'
/*** Put you database name here ***/
    AND S.[database_name] = 'YourDBName'
    AND S.[type] = 'D'
/*** Put your desired recovery date/time here. ***/
    AND S.backup_start_date <= '20240330 13:01:00'
ORDER BY S.backup_finish_date DESC;

homebrew01 SSC Guru Points: 55544 More actions · Answer 12

MarkP wrote:

Thanks for the reply Ant, I was looking for the process and you seem to have hinted at it.
Unfortunately we don't have a .bak from before the target to go at - I'm having to figure out how to in effect rewind a copy of the database back from now to last weekend. Are you saying that one option is:

Are you taking FULL backups now ? You may want to double check that your disaster recovery plan is adequate.