question

Anne-0140 avatar image
0 Votes"
Anne-0140 asked Anne-0140 commented

SSMS restore cannot find the newest backup

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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Anne-0140, we have not get a reply from you. Did any answers could help you? If there has an answer helped, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Anne-0140 avatar image
0 Votes"
Anne-0140 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @Anne-0140,

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:
88075-backup.png

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.




backup.png (87.7 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Go further to next dialogs and try reproducing the original problem with backup sets.

0 Votes 0 ·

Hi @Viorel-1, are you mean in "backup sets to restore" dialogs? We still can see the latest backup files and restored it successfully.
88133-db1.png
88152-db2.png


0 Votes 0 ·
db1.png (50.9 KiB)
db2.png (47.6 KiB)

Thank you.
I am sorry I tried to do a brand new db and reproduced what you have. there is no problem.

Now questions goes to my environment it is a little complicated.
We do the regular maintenance plan each weekend and weekdays on this Dev server.
But during the middle of the week, I restore a copy of production to the dev server.
Then after restore, we do another backup after the restore for the dev environment.
So things may got confused to the SSMS.

Here is the screenshot when I tried to restore the db:
88313-image.png


But if I browse to the backup directory, I can see there are backups in the file system:
88322-image.png


0 Votes 0 ·
image.png (41.5 KiB)
image.png (49.2 KiB)
Show more comments
Anne-0140 avatar image
0 Votes"
Anne-0140 answered Anne-0140 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Anne-0140, could you please try to backup and restore your database in another computer? I made a test and there has no similare issues. Or you could post your issues in SQL server feedback.

1 Vote 1 ·

I tried on another computer and database, if I use SSMS 118.8 in my workstation, if I click database restore
I can see below, the date is March, but I do have May backup.
94084-image.png


If I go to the server use SSMS 17.5 to connect to the same database, it is correct, and has the newest backup copy showing in the list
94092-image.png


0 Votes 0 ·
image.png (36.9 KiB)
image.png (37.7 KiB)