SSMS restore cannot find the newest backup

Anne 256 Reputation points
2021-04-14T17:49:01.03+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,578 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 68,371 Reputation points Microsoft MVP
    2021-04-14T21:32:58.227+00:00

    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.

    No comments

  2. Anne 256 Reputation points
    2021-04-14T21:38:48.85+00:00

    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

    No comments

  3. CarrinWu-MSFT 6,796 Reputation points Microsoft Employee
    2021-04-15T07:04:04.333+00:00

    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:
    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.


  4. Anne 256 Reputation points
    2021-04-16T16:46:43.023+00:00

    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.