SQL Server Management Studio – Backup Not Shown When Restoring

restoresql serversql-server-2012ssms

I am trying to restore a database from a backup taken on another server. I am doing this by using SQL Server 2012 Management Studio.

After I have taken the backup on server 1, I go on to select the database on server 2, choose restore, and I select the database on server 1 as source.

After that, the only backup available is an old one taken by another user. It is from the correct database, but not the backup I just took. The newly created backup is not showing in the list.

The backup I took is copy-only, but the old backup that shows up is copy-only as well. It was another user who took the backup that shows up, but I have administrator rights at both server and I don't think that's the problem (but I can't rule it out). I was able to take the backup on server 1 when using Windows integrated security, so it seems strange if I suddenly don't have rights to view the backup anymore. However, it might be that SSMS is run in a such manner that it doesn't have necessary rights. I am not sure.

I am probably missing something here, but right now I can't see what. What can be possible sources of error to why I can't see my newly taken backup in the list of available backups?

Best Answer

Normally when you want to restore a database from a different server, you're looking to create a device backup to a network drive, and restore from that.

The way the restore from database interface works is by querying the msdb database for recent backups, to see what you can restore. Accordingly you should be using a device backup:

If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.

As the msdb database on server 2 will have no knowledge of the backups taken on server 1.