1

So I'm testing out some of our DR stuff and tried to restore a point in time backup from one of our main databases to a new database on the same server.

I am admin on the box and I'm RDP'd into the box and am using SSMS to do the restore.

When I choose my database the "select the backup sets to restore" window populates with a ton of our 1hr incremental backups all the way back to about 30 days ago.

When I select a random time out of the set I get the error

Cannot Open Backup Device {guid}. Operating System error 2, the system cannot find the file specified.

I'm restoring to a new .mdf and .ldf on a different local drive but I have access to everything.

Not sure what's going on or where to diagnose which file it can't find.

Thanks

marc_s
  • 8,932
  • 6
  • 45
  • 51
user9424763
  • 13
  • 3
  • Have you double checked that the file in fact exists? The "select the backup sets to restore" is pulled from msdb and has no relation to the existence of the physical files. I've had occasions where the maint plan has cleaned up the file I really needed to restore. At that point you have to hope (or plan ahead) you can get it back from tape. – Kenneth Fisher Oct 17 '13 at 14:23

1 Answers1

1

When you open that dialog, SQL Server populates that windows with a lot of backups for the history of backups taken for that database. This history is stored in msdb and is not synchronized with the file system, so it is possible that the backup files themselves were deleted or moved already.

Instead of restoring from that history, select to restore from disk and pick the file(s) yourself.

Restoring a Backup

You need to first restore a full backup. That restore has to happen without recovery.

Then, if you have one, restore the latest differential between the full backup and the point in time you want to restore to. this also has to happen without recovery.

Finally, you need to restore all log backups between that differential (or the full if you didn't have one) and the point in time you want to restore to. Those log backups need to be restored in chronological order, also without recovery. The final log backup you can restore with recovery to bring the database back online.

SQL Server does not force a specific extension for any backup type. Most likely you will have .bak for full backups. Differentials might be either .bak or .dif. For log backups I have seen .trn, .log and others. Some people don't use extensions at all for their backups.

But it is probably a safe guess, that your .trn file is a log backup, so you will need additional backup files to restore that database.

Sebastian Meine
  • 9,125
  • 1
  • 27
  • 32
  • This makes perfect sense since those files don't exist, although if I pick one that does exist it still has a file issue. I will try the route of picking the .TRN file from disk and restoring. Do I need to restore multiple files or will this one .TRN roll everything back to that point? Thanks! – user9424763 Oct 17 '13 at 20:36
  • see edit in my answer above – Sebastian Meine Oct 17 '13 at 23:08
  • I did a full backup from the current state of the database as no full backups were taken apparently. I took this full backup and restored as another database on the same server. Can I use this full backup to apply the log backup? – user9424763 Oct 18 '13 at 13:01
  • No. You can only restore in chronological order. So, with a new full backup only even newer log backups can be restored. And remember, you need a complete log chain. So if you took Full, Log1, Log2, you can restore Log2 only after you restored Full and Log1 in that order. – Sebastian Meine Oct 18 '13 at 13:10
  • Thank you for all your help with this. So this is really starting at a point in time and rolling forward, is there a way to go backwards? Hopefully I am understanding this correctly. – user9424763 Oct 18 '13 at 14:33
  • As long as you have a complete backup chain starting with a full backup you can restore to any point in time between that full backup and the last log backup of that chain. For that however you must stop the restore process at the right time. Once you restored "too far" there is no way to go back, other than starting over with the restore. – Sebastian Meine Oct 18 '13 at 14:42
  • One more thing: Don't forget to show your appreciation by up-voting and/or accepting my answer ;) (http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Sebastian Meine Oct 18 '13 at 14:46
  • I will definitely give you up votes and accepted answers. I just want to see what options I have before I close this. I have the current database which is still running. It has it's main single transaction log that is huge, so I assume it has changes that date back past where I want to go. Can I use this in any way? I only have a subset of incremental backups and no full backups unfortunately. – user9424763 Oct 18 '13 at 15:07
  • If you are looking to recover some lost data, there might be tools that can help with that (see http://dba.stackexchange.com/questions/995/how-do-i-get-back-some-deleted-records/998#998). There is however no build-in solution to go back in time that does not require a full backup to start at. – Sebastian Meine Oct 18 '13 at 16:08