Sql-server – How to get SQL Server Management Studio to find a .bak file to restore

backupdatabasesqlsql server

I am trying to turn a .bak file into a database. I can't find the "Restore Database" button when I left-click "databases" when connected. This is what I should see. However, this is what I see.

I have been using this tutorial to try to restore a database from a .bak file. I placed the .bak file (named WideWorldImporters-Standard) in the SQL Server Management Studio folder. Here's the picture.

Things I've tried:

  • Restarting the Server Management Studio
  • Restarting my computer
  • Looking for updates on Server Management Studio
  • Looking for updates on my computer
  • Copying the .bak file to other folders and trying to open it as a
    file
  • Looking for anything weird going on with azure

I suspect that the problem might be that I am not connecting to the right database. The program seems to be logging into my azure account just fine, but I am not finding any of my existing servers to hook into.

Best Answer

You can restore the database using the backup file using T-SQL:

RESTORE FILELISTONLY   
    FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\AdventureWorks2017.bak' ;  
    GO  

--Restore database from backup


RESTORE DATABASE [AdventureWorks2017]
            FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\AdventureWorks2017.bak'
            WITH  FILE = 1,  
                  MOVE N'AdventureWorks2017'           TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017.mdf',  
                  MOVE N'AdventureWorks2017_log'       TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\AdventureWorks2017_log.ldf',  
                  NOUNLOAD,
                  REPLACE,
                STATS = 20
            GO

Change the folder path and filenames as per your environment. If you get an error of "Permission denied" while executing the code move the .bak file to the default backup directory for the instance. The RESTORE FILELISTONLY is to check the logical names for the database files.

Related Topic