bringing a .bak across the wire without rdp'ing or mapping into the sql server

Question

bringing a .bak across the wire without rdp'ing or mapping into the sql server

stan

Ten Centuries

Points: 1339
More actions
January 30, 2026 at 5:43 pm
Go to Answer
#4726928

hi, a peer of mine would like to be self sufficient in bringing small .bak's over to her local without rdp'ing to the sql server or mapping a drive to that server or asking our dba for help.
I dont even think she would mind if sql sent the .bak straight across the wire to her c: drive.
is there a way running ssms locally but connected to a remote sql server to direct the .bak to a local folder? i usually run this command when i'm backing up to another server's z:drive.
BACKUP DATABASE [xx] TO DISK = N'Z:\Backup\xx.bak' WITH NOFORMAT, NOINIT, NAME = N'DW-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
frederico_fonseca

SSCoach

Points: 16306
More actions
January 30, 2026 at 9:23 pm
Answer
#4727045
you can backup the db to a UNC share - so if she has permissions to do backups, permissions to share a drive with the SQL Server account then she can do it.
another possible way, which i've never tried, is to use openrowset to read the .bak file to a table on the server, then download the content (in binary mode) to a file locally.
```
drop table if exists dbo.backupfile
create table dbo.backupfile
(data varbinary(max)
)

insert into dbo.backupfile
SELECT * FROM OPENROWSET( BULK N'C:\dbname.bak', SINGLE_BLOB ) AS Document;
```
the contents of the table (single row) can then be written to a local file in binary mode using several tools (powershell, C#, SSIS and others)

I trust that she has permission to copy a database to a local pc, and that the data is not sensitive.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 741132 More actions · Answer 1

I don't think there's a way to do this in t-sql. When you connect with SSMS, you're accessing the server and getting results streaming in text. I suppose some sort of sqlcmd, query with xp_cmdshell to stream the contents of the .bak file back as binary to a file on your system, but it might be flaky.

file sync is common. You'd be better off doing something simple like a OneDrive/DropBox/Box/etc. to sync the backups to the cloud and pull them down.

Or a script that copies specific files to a sync place, that you can then delete when you have them

stan Ten Centuries Points: 1339 More actions · Answer 2

i wonder if making that table a temp would get us past any permissions issues we'd have otherwise. now that i look at it closer i dont understand. how does sql know what db to use a s a source? i think you are saying the source is a .bak. and then various etl tools can be used to copy that to a local folder.

This reply was modified 2 weeks, 3 days ago by stan.
This reply was modified 2 weeks, 3 days ago by stan.

frederico_fonseca SSCoach Points: 16306 More actions · Answer 3

go read the manuals. your comment shows a lack of understanding of what the openrowset command is so you need to study it

stan Ten Centuries Points: 1339 More actions · Answer 4

i think we get it frederico. thx.

i think you are saying the source is a .bak. and then various etl tools can be used to copy that to a local folder.