Generally, we could use Restore Database option in SSMS GUI or using RESTORE DATABASE T-SQL command when we want to restore a database from a database backup file (.bak). However, the destination database file (data and log) locations will vary depending on many factors.
Here are details:
1. If we restore the database via SSMS GUI ‘Restore Database…’ option, the database files will be restored to the original file locations if corresponding locations exists on the server. Otherwise, the database files will be restored to the default database file locations of the SQL Server instance. You could find the database file locations of SQL Server in the system registry, for more information see: File Locations for Default and Named Instances of SQL Server.
2. If we restore the database via Restore Database T-SQL command, the database files will be restored to the original file locations no matter these file locations exists or not on the server. Therefore, the database restore options may be failed if same file locations do not exist.
In either ways, we could change the database file locations as required. If using SSMS GUI option, we can change the database file locations in the Options page of Restore Database – <db_name> dialog window. If using T-SQL command, we can use WITH MOVE clause instead.
If you are interested in what are the database file locations stored in the backup file, you could run RESTORE FILELISTONLY T-SQL command to get each database original file location.
Here is an example:
FROM DISK = ‘D:\Tests\BackupTest2.bak’;
and I got the following result: