4

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.

  • 1
    what's the problem with a back up to a file share? seems like the easiest option – Tom V Jul 13 '15 at 15:22
  • I just wondered if there's another way. Maybe SQL + data scripts. – Paul-Sebastian Manole Jul 13 '15 at 15:24
4

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

  1. Make a backup on a swap file on the server
  2. Create a temporary table with a varbinary field
  3. Insert the backup into the table
  4. Fetch column data using ADO.NET
  5. 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.

  • 1
    Thank you for providing the best answer. It's clear how much of a hack this is but seems like the most straightforward solution. – Paul-Sebastian Manole Jul 14 '15 at 20:00
1

With that in mind, I've thought about creating a remote client that would run on my NAS server and that would connect to my different SQL Server instances and issue the job of backing up those databases on a regular basis.

You can use SQLCMD - command line utility to backup your databases on multiple servers. Put SQLCMD in a .bat file and schedule it with windows scheduler.

Refer to KB - 2019698 - How to schedule and automate backups of SQL Server databases in SQL Server Express ?

  • This does not save the backups on the machine that issues the backup command but on the machine the SQL server is running or any UNC that server has access to. – Paul-Sebastian Manole Jul 13 '15 at 18:24
  • 1
    @Paul-SebastianManole why do you think it would save to the server that issued the backup command ? You take backups on the server running sql server. Have the backups taken locally and then move them using powershell or robocopy tools that are external to sql server to a network share or ftp or whereever you want to ship them. – Kin Shah Jul 13 '15 at 18:43
  • I've gone the path of opening a CIFS share to my servers and writing a client that regularly issues the right backup commands to those clients and checks the recovery model just in case. – Paul-Sebastian Manole Jul 14 '15 at 10:27
  • 1
    yes I understand now. But before that I wanted to know if it is possible otherwise. That's basically my question. – Paul-Sebastian Manole Jul 14 '15 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.