Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.92/5 (40 votes)
8 Mar 2009BSD3 min read 139.4K   8.2K   90   46
This article is about transferring backups without raising the privileges of SQL Server, or using custom copying tools.

Image 1

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.

C#
_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.

C#
_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.

C#
_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 :-).

C#
_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

C#
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.

License

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


Written By
Software Developer Agilion Consulting
Poland Poland
I specialize at C#, developing Enterprise solutions. I have some knowledge of ASP.NET MVC - looking forward to use it together with Typescript.

Comments and Discussions

 
QuestionGood work Pin
Member 121175205-Mar-24 3:03
Member 121175205-Mar-24 3:03 
PraiseGreat code, interesting, thanks. Pin
xBulent11-Aug-23 8:36
xBulent11-Aug-23 8:36 
PraiseExcelent code Pin
Member 147905773-Apr-20 14:38
Member 147905773-Apr-20 14:38 
QuestionGreat code Pin
rippergr15-Feb-19 23:40
rippergr15-Feb-19 23:40 
QuestionHow can I run automatic using parameter ? Pin
choi gwang hoon21-Nov-16 22:30
choi gwang hoon21-Nov-16 22:30 
Questionhow to restore from from local machine? Pin
Member 105723234-Feb-14 18:39
Member 105723234-Feb-14 18:39 
AnswerRe: how to restore from from local machine? Pin
Adrian Pasik5-Feb-14 12:02
Adrian Pasik5-Feb-14 12:02 
QuestionWhat should I enter remote dir for temp? Pin
Member 97786087-Jan-14 2:39
Member 97786087-Jan-14 2:39 
AnswerRe: What should I enter remote dir for temp? Pin
Adrian Pasik12-Jan-14 12:45
Adrian Pasik12-Jan-14 12:45 
AnswerRe: What should I enter remote dir for temp? Pin
Adrian Pasik14-Feb-14 11:27
Adrian Pasik14-Feb-14 11:27 
GeneralExcelent 5! :) Pin
Liveeveryday28-Oct-13 8:08
Liveeveryday28-Oct-13 8:08 
QuestionIs your app work over live IP (static IP)? Pin
URVISH_SUTHAR112-Jul-13 3:57
URVISH_SUTHAR112-Jul-13 3:57 
AnswerRe: Is your app work over live IP (static IP)? Pin
Adrian Pasik6-Dec-13 0:54
Adrian Pasik6-Dec-13 0:54 
QuestionBackup Size Limit... Pin
Member 926616710-Oct-12 20:03
Member 926616710-Oct-12 20:03 
AnswerRe: Backup Size Limit... Pin
Adrian Pasik15-Oct-12 0:50
Adrian Pasik15-Oct-12 0:50 
GeneralRe: Backup Size Limit... Pin
chandan kumar2-Aug-18 3:18
chandan kumar2-Aug-18 3:18 
its awesome ! your code helped alot.

Then You have to insert files to separate VARBINARY fields and merge them locally later. -- a humble request if you can share code to merge multiple backup files into single file without SQL server installed where this application will be running , it will be great help for all.

Many Thanks
Chandan
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:50
professionalKanasz Robert24-Sep-12 5:50 
GeneralMy vote of 5 Pin
Ken Richards29-Jun-12 7:00
Ken Richards29-Jun-12 7:00 
GeneralMy vote of 5 Pin
whomwhom8-Jun-12 5:52
whomwhom8-Jun-12 5:52 
Questionfile larger than the ram Pin
tymczasowy3328-Dec-11 2:49
tymczasowy3328-Dec-11 2:49 
GeneralMy vote of 5 Pin
Iman Abidi30-Nov-11 5:10
Iman Abidi30-Nov-11 5:10 
QuestionVery Innovative Pin
Afshar Mohebbi15-Oct-11 9:29
Afshar Mohebbi15-Oct-11 9:29 
GeneralMy vote of 5 Pin
mammadkoma15-Oct-10 23:40
mammadkoma15-Oct-10 23:40 
GeneralThat's great but... Pin
tongngocoanh19-Sep-10 22:11
tongngocoanh19-Sep-10 22:11 
GeneralSQL 2000 Server Pin
pask13-May-09 9:46
pask13-May-09 9:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.