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.