Sql-server – How to restore a database to a point in time using a script in Microsoft SQL Server

sql server

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.

Best Answer

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.

Related Question