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

Improve this question
1
  • 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
    Commented Oct 17, 2013 at 14:23

1 Answer 1

Reset to default
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.

Improve this answer
9
  • 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
    Commented Oct 17, 2013 at 20:36
  • see edit in my answer above
    – Sebastian Meine
    Commented Oct 17, 2013 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
    Commented Oct 18, 2013 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
    Commented Oct 18, 2013 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
    Commented Oct 18, 2013 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.