Sql-server – Getting File not found error doing point in time database restore in SQL Server 2008

recoveryrestoresql serversql-server-2008

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

Best Answer

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.

Related Question