CODEWITHSUNDEEP
sql-serverbackup
Cipher
Cipher

Reputation: 6102

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?

Upvotes: 34

Views: 142588

Answers (11)

Michael Foster
Michael Foster

Reputation: 366

I didn't find it in that standard location, either, but looking at the Properties of the database I saw a backup was indeed taken.

So I right-clicked the database and selected Tasks-Backup... and looked at the destinations. That is where I found my files.

This is using SQL 2012.

Upvotes: 0

Marty
Marty

Reputation: 346

So eventually on SQL Server 2019 you can just call built in SERVERPROPERTY function to query the default backup folder:

select serverproperty('InstanceDefaultBackupPath')

Upvotes: 1

Hink
Hink

Reputation: 1103

Set registry item for your server instance. For example:

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

Upvotes: 3

Matt Roy
Matt Roy

Reputation: 1535

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).

Upvotes: 3

Faiyaz
Faiyaz

Reputation: 1411

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

Upvotes: 1

Matthias
Matthias

Reputation: 373

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

Upvotes: -2

SQLMenace
SQLMenace

Reputation: 135171

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

Upvotes: 54

WraithNath
WraithNath

Reputation: 18013

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

Upvotes: 0

Kenny Evitt
Kenny Evitt

Reputation: 9811

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;

Upvotes: 14

Pascal
Pascal

Reputation: 2984

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

Upvotes: 1

Kenny Evitt
Kenny Evitt

Reputation: 9811

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

Upvotes: 0

Related Questions