August 30, 2007 at 3:07 am
Hi, probably a dumb question but I am just picking up all this SQL Server stuff. A colleague has given me a backup up his db, I have restored it and that is fine. What I want to do ideally is restore it to a few days ago, is that possible? Is all the backup info, whether differential or complete in that one file? It doesn't seem to be the case as I just seem to have backup info regarding my own personal backups.
Like I say, may be a daft question but baby steps...
August 30, 2007 at 3:51 am
Hello Paul,
Backup files don't contain any backup history. Backup history is contained in a table in msdb.
A full database backup is just a snapshot of the database a the time the backup is taken.
A differential backup contains all datapages which have changed since the last full backup.
A transaction log backup contains all transactions since the last backup either full, differential or transaction log.
A point in time restore is only possible if you have all necessary backup files g.e. A full backup plus a transaction log backup. For more info see BOL.
Markus
[font="Verdana"]Markus Bohse[/font]
August 31, 2007 at 3:35 am
If you want to restore your database to what it was like a few days ago, you will need a backup from that time to restore it to.
It does not matter if the backup you need is not shown in the backup history, you can specify 'restore from device' and put in whatever backup file name you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 31, 2007 at 3:44 am
Thanks for the comments, I think Markus' comments sorted me. Sounds like when you copy a backup to another PC, whilst you can restore from the backup, you cannot regress it.
My colleague has a db which is scheduled to take a differential backup each night. I hoped to take a copy of that backup, restore from it on my PC and then regress it to 2 days earlier, which it would appear is not possible as this information is held in the msdb db on the originating SQL Server Instance.
But it was worth a try...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply