question

CaiAaronWing-1844 avatar image
0 Votes"
CaiAaronWing-1844 asked AmeliaGu-msft commented

Restoring SQL Database issue

Hi There,
I am having an issue with restoring a database over another database. So our setup is that we have clients, and they have a live database and a test database. Occasionally we will need to copy the live one over the top of the test one. When trying to restore the live over test, we always will have to head to the files tab, and re-allocate the files. However it is only with one specific database which this happens with. I have identified that the issue is with the live database, however I can't work out how to fix it? Any advice would be appreciated.

Example of issue
144196-how-it-acts.png

Example of how it should be with a different database
144180-how-it-should-act.png


sql-server-general
how-it-acts.png (33.9 KiB)
· 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.

Are you saying that you are expecting to see the "Test" inserted into the "Restore As" and you are not seeing this, and, you are having to add it manually to get the restore to run? Or, are you getting some type of restore error after you manually enter in the "Test" verbage into the physical file name?

0 Votes 0 ·

Hi CaiAaronWing-1844,

How are things going? Does the answer help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

The screen shots are just of the file named which were backed up. There is nothing to "fix" for the file names in the backup.

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Rather than using the UI, use the RESTORE command directly. The form is

´´
RESTORE DATABASE db FROM DISK = '<backuppath>
WITH MOVE '<logicalname1>' TO PATH = '<path1>',
MOVE '<logicalname2>' TO PATH = '<path2>',
MOVE '<logicalname3>' TO PATH = '<path3>'
```You do this once and save the script.

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.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered AmeliaGu-msft edited

Hi CaiAaronWing-1844,

Welcome to Microsoft Q&A.
Could you please check and make sure the following two important potions in Restore database page-> Options tag are unchecked?

144382-image.png

In my little test, once these two options are unchecked, we can restore database over another database successfully.

Best Regards,
Amelia


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.



image.png (37.8 KiB)
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.