0

I have a database that must always restore to the date in which it was created, after test data have been added on it, I get the create_date from the script below:

WITH LastRestores AS
(
SELECT

    [d].[create_date] ,

    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
WHERE [d].[name] = 'MyDatabase'
)
SELECT create_date
FROM [LastRestores]
WHERE [RowNum] = 1

Now what I need to do is run a script that restores the database to the date in which it was created, assuming the date is static/hardcoded in the script i.e: is always '2019-03-08 11:31:50.237' and the database is not restored but stored in a certain folder i.e C:\Documents\BackUps\MyDatabase.bak , now lets say I normaly restore databases like this:

USE [master]
RESTORE DATABASE MyDatabase 
FROM  
DISK = N'C:\Documents\BackUps\MyDatabase.bak' WITH  FILE = 1,  
MOVE N'MyDatabase' TO N'C:\Documents\BackUps\MyDatabase_rows.mdf',  
MOVE N'MyDatabase_log' TO N'C:\Documents\BackUps\MyDatabase_log.ldf',  
NOUNLOAD,  STATS = 5
GO

The only difference now is that it must restore to a point in time which is '2019-03-08 11:31:50.237' using a script.

0

1 Answer 1

1

You need at least two backups for this. One full backup and one transaction log backup. So first you use a command like your above command (but you can remove UNLOAD option, it is a dummy option when you restore from disk). And then you have something like:

RESTORE LOG MyDatabase 
FROM DISK = N'C:\Documents\BackUps\MyDatabase.trn' 
WITH 
 FILE = 1
,STATS = 5
,STOPAT = '20190308 11:31:50.237'

That log backup had to been produced after the point in time to which you want to restore. And since you need a log backup, you need to have the database in FULL (or BULK_LOGGED) recovery mode, not SIMPLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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