Restore to a point in time

Question

I copied over the mdf and ldf for a database (Sql 2005 Express) to my machine and in my Sql 2005 (Developer Edition) attached them. I had set the logging in the source machine to full since I need to research an issue.

Now, in my SQL Server I have 2 database, DAProd that I just attached and DARestore that I just created. I want to restore DARestore with a point in time version of DAProd. When I bring up the Restore dialog, it does not show DAProd in the source dropdown. Any pointers will be appreciated.

Thanks.

Answer 1

In order to perform a point in time restore, you need the various point in time backups of DAProd, not a current copy of DB reattached.

Please See How to: Restore to a Point in Time (SQL Server Management Studio)

Also: Point in Time Recovery

How to: Restore a Database Backup (SQL Server Management Studio)

Answer 2

I assume you have the backup of DAProd (both full and log). You don't need to attach a copy of DAProd, the backup process can create the 'copy' database for you. But is OK if you did attach it, won't matter.

First you run a restore of your most recent full backup of DAProd that is older than the moment in time you want to stop the recovery:

RESTORE DATABASE [DAProd] FROM DISK = '<yourfullbackup>' WITH NORECOVERY;

Next you start to recover the LOG backup that occurred after the full backup but are still prior to the moment in time, one by one:

RESTORE LOG [DAprod] FROM DISK = 'yournextlog' WITH NORECOVERY;

Now you restore the log that contains the moment in time you want to stop:

RESTORE LOG [DAProd] FROM DISK = 'yournextlog' WITH STOPAT = '<timetostop';

The final step is to take the DAProd database online. BTW this will rollback any uncommitted transaction 'in flight' at your moment in time:

ALTER DATABASE [DAProd] SET ONLINE;

You can now look at the database as it was in the moment in time you wish, as if it would roll back any pending transaction at that moment.