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.