[sql-server] Where is database .bak file saved from SQL Server Management Studio?

I was trying to create a backup for my SQL Server Database using SQL Server Management Studio 2008 Express. I have created the backup but it is getting saved at some path which I am not able to find. I am saving it on my local HD and I checked in Program Files>Microsoft SQL Server>MSSQL 1.0>MSSQL>DATA> but its not there.

What's the default save path for this DB .bak?

Use the script below, and switch the DatabaseName with then name of the database that you've backed up. On the column physical_device_name, you'll have the full path of your backed-up database:

select a.backup_set_id, a.server_name, a.database_name, a.name, a.user_name, a.position, a.software_major_version, a.backup_start_date, backup_finish_date, a.backup_size, a.recovery_model, b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name = 'DatabaseName'
order by a.backup_finish_date desc

If the backup wasn't created in the default location, you can use this T-SQL (run this in SSMS) to find the file path for the most recent backup for all DBs on your SQL Server instance:

SELECT  DatabaseName = x.database_name,
        LastBackupFileName = x.physical_device_name,
        LastBackupDatetime = x.backup_start_date
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;

have you tried:

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup

Script to get all backups in the last week can be found at:

http://wraithnath.blogspot.com/2010/12/how-to-find-all-database-backups-in.html

I have plenty more backup SQL scripts there also at

http://wraithnath.blogspot.com/search/label/SQL

...\Program Files\Microsoft SQL Server\MSSQL 1.0\MSSQL\Backup

As said by Faiyaz, to get default backup location for the instance, you cannot get it into msdb, but you have to look into Registry. You can get it in T-SQL in using xp_instance_regread stored procedure like this:

EXEC  master.dbo.xp_instance_regread 
      N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',N'BackupDirectory'

The double backslash (\\) is because the spaces into that key name part (Microsoft SQL Server). The "MSSQL12.MSSQLSERVER" part is for default instance name for SQL 2014. You have to adapt to put your own instance name (look into Registry).

Set registry item for your server instance. For example:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\BackupDirectory

I dont think default backup location is stored within the SQL server itself. The settings are stored in Registry. Look for "BackupDirectory" key and you'll find the default backup.

The "msdb.dbo.backupset" table consists of list of backups taken, if no backup is taken for a database, it won't show you anything

You may want to take a look here, this tool saves a BAK file from a remote SQL Server to your local harddrive: FIDA BAK to local