Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP

• Download demo - 9.2 KB
• Download source - 15.4 KB

Who should read it?

If you have clients with small databases (< 1GB), they have no administrator employed, they are using the Express version of SQL Server, and you want to add some crazy thing to your backup techniques portfolio, read-on :-)

Introduction

A few days ago, a question was asked if my program could backup all (I have embedded backup functionality in my program) data on a specific computer on a company network. The most obvious thing to do was to share a folder on that computer and place the backups on that folder, but the trick was to do it without raising the privileges of SQL Server. The second thing: making a batch file that would copy the backups from one computer to another, but... people wanted to make everything as simple as possible. So... that solution was written :-) (well, not exactly that, this is the basic version of what I wrote).

How the stuff works

I tried to accomplish the task without using anything that is not a standard part of a SQL Server installation; so, I didn't use xp_cmdshell. The code does several steps to accomplish the task.

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 whole magic is done in the method DoLocalBackup which takes two parameters: the path to the catalog on the remote server where we want to create our swap file, and the path to the folder on our local machine where we want to store our backups.

Step 1

In this step, we're going to make a backup into our swap file which is going to be reused the next time we invoke DoLocalBackup, so we don't need to use xp_cmdshell to delete it (since it will be recreated). We will use FORMAT, INIT to make the swap, and COPY_ONLY to ensure we did not mess up with the other backup plan.

_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

In this step, we will create our temporary table to store the backup information from our swap file.

_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

Now, we're going to load our backup information into a temporary table. I used OPENROWSET with BULK and it worked prefectly. You can do some fun stuff with OPENROWSET, it's really worth reading about it.

_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();

Steps 4 and 5

Fun stuff :-). We're getting one row from the temporary table and fetching it as a stream of bytes. This is kind of tricky because MSDN tell us that we should set the size as GetUpperBound(0). Correct me if I'm wrong, but that is possibly an error because when you do that, you won't copy the last byte, and later the the file might be corrupted (especially if some CRC calculations are taking place). So I just added + 1, and my backups restore flawlessly :-).

_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 complete backup method

public void DoLocalBackup(string AremoteTempPath, string AlocalPath)
{
    try
    {
        if (_conn == null)
            return;
        SqlCommand _command = new SqlCommand();
        _command.Connection = _conn;
        // nice filename on local side, so we know when backup was done
        string fileName = _dbname + DateTime.Now.Year.ToString() + "-" +
            DateTime.Now.Month.ToString() + "-" +
            DateTime.Now.Day.ToString() + "-" + 
                DateTime.Now.Millisecond.ToString() + ".bak";
        // we invoke this method to ensure we didnt mess up with other programs
        string temporaryTableName = findUniqueTemporaryTableName();
         
        string _sql;

        _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();
        _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();
        _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();
        _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();

        _sql = String.Format("DROP TABLE ##{0}", temporaryTableName);
        _command.CommandText = _sql;
        _command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        throw;
    }
}

Some other code that I use

This article uses components from my other article. It basically adds a label for every control you put on a form. In my humble opinion, a great tool if you do not enjoy placing labels every time you use controls. It does some boring tasks automagically :-) So if you have some spare time, read this article, vote it, leave a comment. Feedback is greatly appreciated.

Points of interest

Well, I surely learned one thing: do not believe blindly in documentation :-). I spent some time trying to fix that 1 byte error :) (I think that SQL Server does some CRC calculations). Also, what I like about this solution is that it saves me time. No backup and copy, just press a button.

History

• 7 March 2009 - First version.

This article, along with any associated source code and files, is licensed under The BSD License