How to backup MS SQL Server databases off-site?

Microsoft SQL Server supports backing up to remote network locations only by means of UNC names the SQL Server (and user account it's running under) has access to.

Is it possible to create backups and stream them back to a SQL client by means of using the same database connection?

I think you would have to pull the database schema and data, then join those together to create a SQL script that can be used to recreate the database being backed up, but is this a viable solution to backing up SQL Server databases?

PS: I know there are commercial solutions that offer off-site back-ups but I'm not interested in commercial solutions at the moment.

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.

StackExchange Database Administrators Q#106780, answer score: 4