sys.database has old databasename after restore

Question

After restoring my Azure database I have several issues. Azure data studio won't show tables, SSMS will not create context based scripts. Trying to rename produces an error message: The database does not exist. I suspect this is caused by a name mismapping of some kind:

• A query to list database names from sys.databases ('select name from sys.databases') returns:
  • the new database name when executed from master
  • the old database name when executed from my new database

Answer 1

Hi Terje Lundsten (work)
It sounds like you're having a frustrating issue with your Azure database restore, especially with the database name mismatch. Here’s what you should consider:

1. Name Mapping Issue: When you restore a database, the name may not update correctly due to how the database files are being referenced. The command select name from sys.databases shows the new name from the master database but the old name when executed from within the restored database, indicating a potential name resolution issue during the restore process.
2. Restore Operation: If the database was restored without renaming it, it could still be holding onto metadata from its previous instance. You might want to check the restore operation to ensure it explicitly specifies the new database name. Refer to the documentation for restoring a database with a new name: Restore a Database with a New Name.
3. File Mapping: When restoring from a backup, make sure that the file paths and names in the backup do not conflict with existing database files. If you're moving or renaming the database files, use the WITH MOVE option to specify new file paths and names. This is crucial if the files on the destination server already exist.
4. Database Ownership: After a restore, the user who initiated the restore becomes the owner of the new database. You may want to verify that the necessary permissions and ownerships are correctly set.

Here are a couple of steps you can try:

• Verify Restore Command: Double-check the syntax and parameters of your restore command, ensuring it's using the correct database name and file paths.
• Use RESTORE FILELISTONLY: Run RESTORE FILELISTONLY FROM <backup_device> to list the logical and physical names of the backup files and ensure they align with your restore commands.

If you're still running into issues, here are a few follow-up questions that could help narrow down the problem:

1. What command or method did you use to initiate the restore?
2. Did you specify a new name during the restore process?
3. Are there any existing databases with the same name or files that could be causing conflicts?
4. Have you checked if all necessary permissions are in place for the user executing the restore?

Hope this helps you get back on track! Let me know if you have any other questions.