Goal
To Restore a Database to a point in time and also to a new Database. Restore of a database is simple, restore of a transaction log is simple also, but restore to a point of time and to a new database takes some doing. It is not well documented either, or better put there are so many parameters to the Restore statement that it gets lost in translation.
Environment
The server was 2008 R2. SQL server was 2008 R2.
Issue
A client contacted me and said that he had deleted a batch in an accounting system that has SQL Server as a back end. I found that we set up the backup plan to do a full backup in the evening and a transaction log backup from 7 AM to 7 PM every 4 hours. The client informed me that the batch was deleted around 10:30. We did not want to restore the entire database. Too many things happened since then and he didn’t realize the batch was deleted till late in the day. The plan was to restore the database to a new database and copy the batch in from the tables using transact sql.
To do point in time you have to use Trasact-SQL Statements. You cannot do it through the interface. The issue was to get the proper syntax to restore to a new database and a point in time. I also had issues figuring the proper syntax for the with StopAt date that determines when the restore stops.
Resolution
To restore to a new database you use a MOVE statement for each file contained in the original database. You do the MOVE in the restore of the last full backup. The syntax is WITH MOVE ‘Name of Old File in SQL’ To ‘Name and Path of New Database File’. At minimum
You would have a mdf and a log:
WITH MOVE ‘Database_Data’ To ‘C:\...\NewDatabase.MDF’,
MOVE ‘Database_Log’ To ‘C:\...\NewDatabase.LDF’
Notice the MOVE’s are comma delimited.
To Restore to a point in time you are usually restoring a full backup then several log files till you reach the log file with your cut off time. To do that you must do the full backup restore WITH NORECOVERY (keep off line), each full transaction log must be WITH NORECOVERY, and the final transaction log WITH RECOVERY (bring whole thing back on line).
Finally the last Transaction log needs to have a way to tell the backup to stop restoring after a certain time in the log. You do that with a WITH STOPAT=’Date and time’ statement.
I had problems that my restore was restoring the full backup of the last transaction file. It seemed like it was ignoring my date. I put syntax like:
With STOPAT = '08/10/2012 10:22:00'
And many other date syntax. All seemed to ignore my date.
Finally I did a select GetDate() and looked at how SQL formatted the date:
'YYYY-MM-DD HH:MM:SS.MMM'
Once I changed my date to format just like SQL did in Enterprise Manager, the stop at started working.
See my full restore statements below:
RESTORE DATABASE NewDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_020001_7099795.BAK'
WITH
MOVE 'OldDatabase_Data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewDatabase.mdf',
MOVE 'OldDatabase_Log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewDatabase.ldf',
NORECOVERY
GO
RESTORE LOG GetBatch FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_070000_8603788.TRN'
WITH NORECOVERY
GO
RESTORE LOG GetBatch FROM DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\OldDatabase\OldDatabase_backup_2012_08_10_110000_8897061.TRN'
With STOPAT = N'2012-08-10 10:22:00.000'
, RECOVERY
GO
Conclusion
Restore to a new database to a point in time is possible as using the Move and With statements. You also need to keep your STOPAT date formatted properly. 