Search code examples
sql-serverssms

I can not restore my database SQL Server Management Studio can't recognize my .bak files

I'm trying to restore a DB from the class. However, when I try to restore the .bak file, it seems like SSMS doesn't recognize it.

I gave full permissions to the folder which contains the .bak file (it is the default backup folder of ms SQL studio).

Steps I've taken to restore the .bak file:

Right click on DB -> Restore DB -> From device (selected the .bak file location) -> To Database (selected the DB destination)

Like here: C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup

The DB is not corrupt I checked 2 other DB same result so I have no idea what is the problem

Here images:

enter image description here

I can not see the DB and I have almost the latest version of MS SQL studio so I can not understand what is going on here and why I can not restore DBs

I checked here: restore database in ssms 2017 selected bak file in device option and showing nothing in Backup sets to restore option and disable Ok button too

and here: SQL Server Management Studio can't recognize .bak file

https://www.youtube.com/watch?v=U0FpXwQfBaU

It should be easy and simple like the video above but like I said above something is wrong here and I do not know what is it. Image of my SQL version

enter image description here

Solution

  • Once you (i mean the service account) got full permissions on the folder where the .bak file sitting and file not corrupted you should be able to restore without any issue, but there are times things fail in GUI and work perfectly with command line. not sure the issue might be exists one of the fix of SSMS release notes

    However, you probably want to try following:

    1. Verify the backup file - it does the verification of backup file and detects any error/corruption within backup file
    RESTORE VERIFYONLY FROM DISK = 'C:\YourbackupLocation\DbName.bak';
    1. Read the header - it returns information of backup file i.e. DB Version, appended backup files, LSN info and database recovery model etc..
    RESTORE HEADERONLY FROM DISK = 'C:\YourbackupLocation\DbName.bak';
    1. Get list of file names from backup - This will be helpful if the file location is different from the source server from where the backup was created 
    RESTORE FILELISTONLY FROM DISK = 'C:\YourbackupLocation\DbName.bak' ;
    1. Restore backup - actual restore via T-SQL
    RESTORE Backup DBName FROM DISK = 'C:\YourbackupLocation\DbName.bak' with replace, recovery, stats;

    To relocate files

    RESTORE Backup DBName FROM DISK = 'C:\YourbackupLocation\DbName.bak' 
with replace, recovery, stats
--- Get the logical name from the result of "RESTORE FILELISTONLY" command
move 'DBName' to 'C:\NewLocation\DBName.mdf',
move 'DBName_Log' to 'C:\NewLocation\DBName_log.ldf' ;