Where is database .bak file saved from SQL Server Management Studio?

Question

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?

Answer 1

Should be in

Program Files>Microsoft SQL Server>MSSQL 1.0>MSSQL>BACKUP> 

In my case it is

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

If you use the gui or T-SQL you can specify where you want it T-SQL example

BACKUP DATABASE [YourDB] TO  DISK = N'SomePath\YourDB.bak'  WITH NOFORMAT, NOINIT,  NAME = N'YourDB Full Database Backup',  SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO 

With T-SQL you can also get the location of the backup, see here Getting the physical device name and backup time for a SQL Server database

SELECT          physical_device_name,                 backup_start_date,                 backup_finish_date,                 backup_size/1024.0 AS BackupSizeKB FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id WHERE database_name = 'YourDB' ORDER BY backup_finish_date DESC 

Answer 2

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;