Point in time restore, restoring past the point in time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Point in time restore, restoring past the...

14 posts, Page 1 of 2

1 2 »»

Point in time restore, restoring past the point in time

Rate Topic

Display Mode

Topic Options

Author

Message

Sydknee

Posted Monday, November 05, 2012 2:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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

Post #1380917

anthony.green

Posted Monday, November 05, 2012 2:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1380918

Sydknee

Posted Monday, November 05, 2012 2:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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

Post #1380919

anthony.green

Posted Monday, November 05, 2012 2:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380921

Sydknee

Posted Monday, November 05, 2012 2:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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

Post #1380928

Sydknee

Posted Monday, November 05, 2012 2:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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.

Post #1380930

anthony.green

Posted Monday, November 05, 2012 2:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380931

Sydknee

Posted Monday, November 05, 2012 2:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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

Post #1380934

anthony.green

Posted Monday, November 05, 2012 2:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1380936

Sydknee

Posted Monday, November 05, 2012 2:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:44 AM
Points: 7, Visits: 20

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.

Post #1380938

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions