SSMS restore cannot find the newest backup

Question

I am using SQL server 2019, SSMS 18.5 and 18.8.

1. I did a full backup of database manually for databaseA on SSMS on my workstation.
2. Then I tried to restore the databaseB from the backup of DatabaseA I just did.
3. From the restore menu of SSMS, in the backup restore sets list window, I cannot see the backup I just did in 1. It listed those backed up earlier through sql agent job.
4. But on my server I have SSMS 17.5 or 17.4 I remote into my server and I went to the same restore window, and I can see the backup created in 1 was listed there.
5. if I restore from device, and browse to where the backup file is, the restore works that way too.

What could be wrong about 3.

Answer 1

You do know the name of the backup file, don't you?

RESTORE DATABASE DatabaseB FROM DISK = 'C:\temp\DatabaseA.bak'
WITH MOVE 'DatabaseA' TO '<where you want the data file for databaseB>',
      MOVE 'DatabaseA_log' TO '<where you want the log file for databaseB>'

MOVE should be followed by the logical names of the files in the database. Often the follow the pattern I have shown here, but you can use sp_helpdb on the source database to find out for sure. Or RESTORE HEADERONLY on the backup file.

But stay away from the UI. It's only confusing.

Answer 2

Thanks, I have no problem to use tsql to restore database.

I just tried to figure out if it is a bug in SSMS 18.x, since 17.X works,

Sometime I do use the SSMS UI to figure out quickly what are a series chain of database backups I need to restore to a point of time, which include, full, differential and a series of transaction log backups.

Since it is a SSMS makes that UI available for restoring database, if it is a bug, then it should be fixed to avoid confusion, correct?

Thanks

Answer 3

Hi @Anne ,

Welcome to Microsoft Q&A!

I use SQL server 2019 and SSMS 18, and have 2 databases (SQLTestDB and SQLTestDB2). I just backed up SQLTestDB, and then try to restore SQLTestDB2 by using SSMS UI, we can saw all the backup file in the folder as below:

I think it should not be a bug, but we suggest you to use T-SQL to restore database. You could find the details of bug fix from Release notes for SQL Server Management Studio (SSMS).

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Sorry, I have to post in answer since the reply for comment says it exceeds 1000 characters.

Thank you. Glad you could verify that part.
For mine, it is a little complicated, probably the detail made the difference, but I cannot find what caused that.
Like I said we have regular maintenance plans on backup the dev environment.
But in the middle of the week I setup an auto restore from production db backup (copy-only bk) to this dev db.
And right after restore there is an auto backup job (not copy only) through T-SQL script on the dev server.
And then after that the regular maintenance plan backup goes on that included weekend full, daily differential and 3 hours transaction log backup.
I wonder during this process something makes it wrong, but according to the logic, the back up chain should continue. not sure which part goes wrong.
Again, I told I can use T-SQL, and why I use the SSMS for special occasions in above post, and I can use 17.X without problems comparing with 18.X
But my goal is to see if this is some defect of SSMS, so that it won't cause confusion for users.