SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Point in time restore, restoring past the point in time / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 16:51:15 GMT20RE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxJust had the same problem here, someone accidentally deleted 7000 rows from a table so i was asked to do a restore to the dev server so we could get the data back.I set it up in Management Studio to restore to 14:48, selected the relevant logs and set it off, only for it not to stop at 14:48 and restore to the end of the last log. I set up the restore again and scripted it out rather than running it to find that there was no stopat clause, I added it myself and ran the script and the restore worked.My boss is telling me that using point in time via management studio only works if you're restoring over the same database or on the same server as the backup was taken. In this case it was a backup from live, restored to a dev database on a dev server it didn't work. He's not great at explaining stuff so if anyone could enlighten me on this i'd appreciate it.Tue, 06 Nov 2012 10:13:59 GMTMikeYanagitaRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxCan you post a screen shot of the GUI so we can verify what options you should be selecting? Otherwise like Anthony stated T-SQL is the way to go. It is also much easier to use if you have several log files you need to restore IMO.Mon, 05 Nov 2012 07:17:58 GMTJoie AndrewRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxThanks for your help.Not good that the GUI doesn't work especially for people like me that don't have the knowledge to do it via scripts.Mon, 05 Nov 2012 03:10:18 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxRestore database.I never trust the GUI to do exactly what it says it is going to do, I prefer to do things in script, which is why that script button is a big thing for me as it allows me to see what the GUI is actually doing and I can make modifications when it misses little things out.Mon, 05 Nov 2012 02:53:39 GMTanthony.greenRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxWhen doing a Point In Time Resore should I be selecting Restore Database or Files and File Groups?I was tought to select Restore Database but I was tought by an Oracale DBA and they only had a little SQL Server knowledge.Mon, 05 Nov 2012 02:45:45 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxI just did a restore via the GUI and it did pick up the STOPAT date time I supplied. Strange that its not doing it on your side.Mon, 05 Nov 2012 02:39:32 GMTanthony.greenRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxThanksWhat I can't understand is that if you put the date and time in the "To a point in time:" box why this is not setting that date and time in the script.Seems to me that the box is a total waste of time if it doesn't do what it is supposed to. ThanksChrisMon, 05 Nov 2012 02:34:58 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxThere is the problem, the last log restore should specify the STOPAT clause detailing SQL where to stop restoring the log, you can manually add it in at the end of the last log restore, or run through the GUI telling it where to stop the restore at.Mon, 05 Nov 2012 02:28:48 GMTanthony.greenRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxFrom my limited knowledge looking at that script it appears that it is restoring the whole of the last transaction log and not to the time specified.Mon, 05 Nov 2012 02:28:26 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxRESTORE DATABASE [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_000008_4358053.bak' WITH FILE = 1, MOVE N'Pr2company1' TO N'P:\Test Data\MRCTest', MOVE N'Pr2company1_log' TO N'P:\Test Log\MRCTest', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_010002_6052447.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_020006_4467461.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_030001_6887170.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_040001_0529897.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_050007_6089085.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_060006_8015801.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_070007_2890600.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_080001_5127476.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_090006_5086564.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_100004_5469206.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_110001_5243780.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_120006_3799713.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GORESTORE LOG [MRCTest] FROM DISK = N'Z:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Pr25Company1\Pr25Company1_backup_2012_11_02_130003_6090541.trn' WITH FILE = 1, NOUNLOAD, STATS = 10GOMon, 05 Nov 2012 02:25:43 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxRun through the restore again in managment studio but instead of hitting run, hit the button at the top and script it to a new query window and post the output, then cancel the restore GUI screen.Mon, 05 Nov 2012 02:09:52 GMTanthony.greenRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxI did this through Studio Manager (I'm not that cleaver!)Mon, 05 Nov 2012 02:08:03 GMTSydkneeRE: Point in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxWhat was the command that you used to restore to the point in time?Mon, 05 Nov 2012 02:06:56 GMTanthony.greenPoint in time restore, restoring past the point in timehttp://www.sqlservercentral.com/Forums/Topic1380917-391-1.aspxI've done a search and couldn't find any postings for this.I recently did a point in time restore to a fresh database from our main live database to try and replicate a user error.The error happened (so I was told around 12:50) so I restored the database to 12:45.When restored it showed changes to the database that had occurred at 12:59.I restored it again to 11:59:59 so that the last transaction log backup wasn't restored and all was fine.My understanding of PIT restore should only restore to the time you specify and not the full transaction log.Has anyone else come across this issue before?ThanksMon, 05 Nov 2012 02:03:53 GMTSydknee