CODEWITHSUNDEEP
sql-serverdockert-sqldatabase-backupssql-scripts
CoderLee
CoderLee

Reputation: 3479

How to view .bak file from DATABASE BACKUP in SQL Server on Docker

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.

Upvotes: 0

Views: 2211

Answers (2)

Mariano Demarchi
Mariano Demarchi

Reputation: 345

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.

Upvotes: 0

CR241
CR241

Reputation: 2603

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

Upvotes: 1

Related Questions