0

Is it possible for me to restore a SQL Server 2019 database up to a particular datetime? The database backup is full backup.

The database is full recovery mode, and only one transaction log backup after the full backup. Thanks.

2
  • Yes, have you read the documentation? It comes with plenty of Examples
    – Thom A
    Jan 22, 2021 at 12:37
  • You would have to restore the entire full backup with norecovery and then restore transaction logs using the STOPAT parameter. Then you would bring the database online by finally issuing restore database with recovery. Jan 23, 2021 at 12:00

1 Answer 1

0

You can do it through T-SQL this way:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH NORECOVERY
GO 
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' 
WITH RECOVERY, 
STOPAT = 'Mar 23, 2009 05:31:00 PM' 
GO

But don't forget you can use the GUI from SSMS. The graphical solution is easier to understand especially if you are 4 people around the same monitor and the manager is behind you with a stick in his hand:

  1. Right click on the database > Tasks > Restore > Database

enter image description here

  1. General > Timeline and setup the time for database restoration

enter image description here

  1. Check in “Specific date and time” to set up the time for database restoration, press “OK” to save changes, and press “OK” button in the previous window

enter image description here

2
  • 1
    He cannto do this as all he has is full backup. First, we don't know if his database is in full recovery mode, second he has no transazction log backups
    – sepupic
    Jan 25, 2021 at 11:05
  • @sepupic, thank you, let me rephrase that Jan 25, 2021 at 12:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.