How to Copy a file using SQL Server in three easy steps

Might happen that you can't access SQL Server shared drives, but you desperately want to copy file from the server to another location.

Here is the solution I have:

Step 1. Reading a file.

At first, you have to read the file you want to copy into a SQL Server. You have to choose a database to perform that action. It can be Test database or you can create a new database to perform that action or it can be even TempDB. There is only two requirements for the database:
- It must not be a production Database;
- Database should have enough of space to accommodate the file you want to copy.

So, as the firs step in copying we create a table in TempDB and read backup file into that table:

USE tempdb
GO
DROP TABLE IF EXISTS tbl_StoredFile;
GO
CREATE TABLE tbl_StoredFile(StoredFile VARBINARY(MAX));
GO
INSERT INTO tbl_StoredFile(StoredFile) SELECT BulkColumn
FROM OPENROWSET(BULK'C:\Backup\AdventureWorks2014.bak', SINGLE_BLOB) AS x;
GO
sp_spaceused tbl_StoredFile;
GO 

Step 2. Extract a File.

From this point you are going out of SSMS and run a NT Shell commands.
At first you press Win+R, then type "CMD" and then press enter.
That has to open new "CMD.EXE" window.
You have to choose a folder on your computer where you will extract your file.
You also have to provide SQL Server name and credentials to it:

bcp "SELECT StoredFile FROM tempdb.dbo.tbl_StoredFile;" queryout "L:\Temp\AdventureWorks2014.bak" -S <ServerName> -U <UserName>

- After you fill server name and user name and press Enter, BCP utility will ask you for your password.
- Then it will ask you to enter "Enter prefix-length of field StoredFile [8]:" - By default it is "8", but you have to enter "0" and press enter.
- Then it will ask you for "Enter length of field StoredFile [0]:" - just press enter;
- Then it will ask you for "Enter field terminator [none]:" - just press enter;
- Then it will ask you if you want to save this format information - answer "n" and press Enter.

As the result, file will be extracted to your file system.

Step 3. Cleanup.

Just cleanup the space in your database by dropping the table:

USE tempdb
GO
DROP TABLE tbl_StoredFile;

As you can see it is extremely easy to copy files using SQL Server, you just need to have enough of free space in a database to load a file.