February 8, 2006 at 2:24 pm
If I have a database and want to restore it under a new server to a certain point in time - how do I do that?
I tried making a full backup of the database an log and then use restore from disk, norecovery, restore from log with recovery, stopat
But stopat seems to be ignored.
No backups was done previously
February 8, 2006 at 2:46 pm
Hello Henrik,
Check out "STOPAT clause" in Books Online. Have you given any time to stop at that particular time or not?
Thanks and have a nice day!!!
Lucky
February 8, 2006 at 3:00 pm
Its not clear from your post - was your full backup taken before or after the stopat time?
A full backup reflects the database at the time that the backup finishes. There's no going back from there if there are no earlier backups. What you need is a timeline sequence of Full Backup, (optional) Incremental backup, Point-in-time, Log Backup(s). If this is the sequence of backups, your restore strategy should work to get you back to Point-in-time.
February 8, 2006 at 3:19 pm
After.
So i guess the only option is using some third party tool?
SQL Server 2005 does not have any improvments regarding rolling back changes to a certain time?
February 8, 2006 at 10:59 pm
Hi Henrik. I think you may be confused as to what roll back means. When you restore a full database backup, it has an exact copy of the database in that point in time. This may also have changes made by uncommited transactions. When you switch out of recovery mode during a db restore (after restoring transaction logs, for example), it will roll back any changes in the full backup of the database that don't show a commit transaction in the t-log. By design, SQL Server (2000, 2005) does not undo commited transactions from a full backup using transaction logs (i.e. moving backwards in time from the full backup).
That being said, it may be technically possible to do what you are suggesting, and there is a software product that can do a lot with t-logs called Log P.I. They may be worth checking out.
Thanks,
Eric
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply