0

How does one go about viewing a .bak file when they script out a backup command? For example when I run a script that has this SQL in it:

DECLARE @database NVARCHAR(75) = 'MyDatabase';
DECLARE @filename NVARCHAR(75) = 'C:\temp\MyDatabase.bak';
BACKUP DATABASE @database
TO DISK = @filename;

I get a message saying that backup was complete, however, I can't go to the location and view the .bak file. SSMS can pickup that it's there when I restore from device > file, but I can't view the file either in the window explorer or PowerShell prompt. How can one see the backup file that's being created?

Edit

The database is a local MS SQL Server Express running inside of a docker container. I think that should still act like the normal instance of SQL Server, but maybe someone can explain how it differs.

CoderLee
  • 3,079
  • 3
  • 25
  • 57
  • Is the database in your local? Or is it a remote DB server? – Kristianne Nerona Oct 15 '20 at 21:02
  • 1
    The backup file is created **on the machine where SQL Server runs** - so if you're connected to a remote SQL Server - it's on that computer's `C:\` drive .... – marc_s Oct 15 '20 at 21:09
  • The database is on the machine I'm using, so it's local and not backing up to a remote server anywhere. – CoderLee Oct 15 '20 at 21:41
  • Does [List hidden sub-directories and sizes](https://stackoverflow.com/questions/29688848/list-hidden-sub-directories-and-sizes) help? – HABO Oct 15 '20 at 21:45
  • @HABO not at all, it also doesn't show it. Seems like only SSMS knows it exists... – CoderLee Oct 15 '20 at 21:48

2 Answers2

1

If you have access to the SQL Server instance where the backup was originally run, you should be able to query msdb:

SELECT * FROM  msdb.dbo.backupset  
WHERE database_name = 'MyDBname' AND type = 'D'

Here is the query that will give me the physical device name, backup start date, backup finish date and the size of the backup.

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 = 'YourDBname'
ORDER BY backup_finish_date DESC
CR241
  • 2,293
  • 1
  • 12
  • 30
  • I have done that as well, but the file is invisible to a command prompt, Power Shell terminal, or file explorer even though it shows up inside of that query result. How can you view the file at the location(s) in the result(s)? – CoderLee Oct 15 '20 at 21:40
1

You're not able to see the backup file because it was taken in the SQL instance running in the Docker container. When you run the script to take the backup, the backup is stored in the storage available for the SQL instance (not for the SSMS). Check how to access here: Accessing Docker container files from Windows or how to copy files from/to the docker container here: https://www.youtube.com/watch?v=ht4gqt4zSyw.

Mariano Demarchi
  • 339
  • 1
  • 5
  • Since the files actually live in a Docker container, then how is it that SSMS can view the files from the file system on Windows when they aren't actually there? – CoderLee Oct 16 '20 at 00:32
  • Are you sure that the instance of SSMS does not belong to the SQL Server installation? Do the test of saving a query from SSMS as a file and try to look for that file using the file explorer. – Mariano Demarchi Oct 16 '20 at 01:36
  • Yes, 100% and it saves the .sql files to local disk as expected. I saved a `SQLQuery1.sql` to Documents > SQL Server Management Studio, and it appears there after saving. – CoderLee Oct 16 '20 at 13:39