Point in time restore, restoring past the point in time

  • I'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?

    Thanks

  • What was the command that you used to restore to the point in time?

  • I did this through Studio Manager (I'm not that cleaver!)

  • Run 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.

  • RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

    RESTORE 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 = 10

    GO

  • From 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.

  • There 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.

  • Thanks

    What 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.

    Thanks

    Chris

  • I 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.

  • When 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.

  • Restore 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.

  • Thanks 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.

  • Can 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.

    Joie Andrew
    "Since 1982"

  • Just 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.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply