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 gt Microsoft SQL Server gt MSSQL 1 0 gt MSSQL gt DATA gt  but its not there    What s the default save path for this DB  bak

User · Answer

Program Files Microsoft SQL Server MSSQL 1 0 MSSQL Backup

User · Answer

Set registry item for your server instance  For example   HKEY LOCAL MACHINE SOFTWARE Microsoft Microsoft SQL Server MSSQL 2 MSSQLServer BackupDirectory

User · Answer

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

User · Answer

Should be in  Program Files gt Microsoft SQL Server gt MSSQL 1 0 gt MSSQL gt BACKUP gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to backup