0

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?

  • How are you able to see the databases on server 1 from server 2 in the ssms backup restore window? Are you sure you're not just seeing a backup from server 2? – Reaces Aug 12 '15 at 8:00
  • I would just not use the crippled UI for this at all. RESTORE DATABASE ... FROM DISK = '\\unc\share\'; should work just fine as long as you have access to the share... – Aaron Bertrand Aug 12 '15 at 13:37
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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