I'm not sure I would advocate it as a good solution, neither performance wise nor as a best practice, but there actually is a possibility.
In short, the steps would be
- Make a backup on a swap file on the server
- Create a temporary table with a varbinary field
- Insert the backup into the table
- Fetch column data using ADO.NET
- Save the stream in a file
The full source code is available on codeplex Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP
The gist of the code is:
Step 1:
_sql = String.Format("BACKUP DATABASE {0} TO DISK " +
"= N'{1}\\{0}.bak' WITH FORMAT, COPY_ONLY, INIT, " +
"NAME = N'{0} - Full Database Backup', SKIP ",
_dbname, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
Step 2:
_sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS " +
"NOT NULL DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))",
temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
Step 3:
_sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET" +
"(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck",
temporaryTableName, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
Step 4 & 5:
_sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
SqlDataAdapter da = new SqlDataAdapter(_sql, _conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
byte[] backupFromServer = new byte[0];
backupFromServer = (byte[])dr["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0) + 1;
FileStream fs = new FileStream(String.Format("{0}\\{1}",
AlocalPath, fileName), FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(backupFromServer, 0, aSize);
fs.Close();
The original code was written by Adrian Pasik under the bsd license and published on codeplex.
