Click here to Skip to main content
11,641,416 members (61,352 online)
Click here to Skip to main content

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

, 8 Mar 2009 BSD 74K 4.7K 85
Rate this:
Please Sign up or sign in to vote.
This article is about transferring backups without raising the privileges of SQL Server, or using custom copying tools.

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 Smile | :)

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 Smile | :) (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 Smile | :) . 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 Smile | :) .

_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 Smile | :) 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 Smile | :) . I spent some time trying to fix that 1 byte error Smile | :) (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

Share

About the Author

Adrian Pasik
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.

You may also be interested in...

Comments and Discussions

 
Questionhow to restore from from local machine? Pin
Member 105723234-Feb-14 18:39
memberMember 105723234-Feb-14 18:39 
AnswerRe: how to restore from from local machine? Pin
Adrian Pasik5-Feb-14 12:02
memberAdrian Pasik5-Feb-14 12:02 
QuestionWhat should I enter remote dir for temp? Pin
Member 97786087-Jan-14 2:39
memberMember 97786087-Jan-14 2:39 
AnswerRe: What should I enter remote dir for temp? Pin
Adrian Pasik12-Jan-14 12:45
memberAdrian Pasik12-Jan-14 12:45 
AnswerRe: What should I enter remote dir for temp? Pin
Adrian Pasik14-Feb-14 11:27
memberAdrian Pasik14-Feb-14 11:27 
GeneralExcelent 5! :) Pin
Liveeveryday28-Oct-13 8:08
memberLiveeveryday28-Oct-13 8:08 
QuestionIs your app work over live IP (static IP)? Pin
URVISH_SUTHAR112-Jul-13 3:57
memberURVISH_SUTHAR112-Jul-13 3:57 
AnswerRe: Is your app work over live IP (static IP)? Pin
Adrian Pasik6-Dec-13 0:54
memberAdrian Pasik6-Dec-13 0:54 
QuestionBackup Size Limit... Pin
Member 926616710-Oct-12 20:03
memberMember 926616710-Oct-12 20:03 
AnswerRe: Backup Size Limit... Pin
Adrian Pasik15-Oct-12 0:50
memberAdrian Pasik15-Oct-12 0:50 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:50
mvpKanasz Robert24-Sep-12 5:50 
GeneralMy vote of 5 Pin
Ken Richards29-Jun-12 7:00
memberKen Richards29-Jun-12 7:00 
GeneralMy vote of 5 Pin
whomwhom8-Jun-12 5:52
memberwhomwhom8-Jun-12 5:52 
Questionfile larger than the ram Pin
tymczasowy3328-Dec-11 2:49
membertymczasowy3328-Dec-11 2:49 
GeneralMy vote of 5 Pin
Iman Abidi30-Nov-11 5:10
memberIman Abidi30-Nov-11 5:10 
QuestionVery Innovative Pin
afsharm15-Oct-11 9:29
memberafsharm15-Oct-11 9:29 
GeneralMy vote of 5 Pin
mammadkoma15-Oct-10 23:40
membermammadkoma15-Oct-10 23:40 
GeneralThat's great but... Pin
tongngocoanh19-Sep-10 22:11
membertongngocoanh19-Sep-10 22:11 
GeneralSQL 2000 Server Pin
pask13-May-09 9:46
memberpask13-May-09 9:46 
GeneralRe: SQL 2000 Server Pin
Adrian Pasik13-May-09 9:59
memberAdrian Pasik13-May-09 9:59 
GeneralRe: SQL 2000 Server Pin
pask16-May-09 8:21
memberpask16-May-09 8:21 
Generalvery smart approach... Pin
Member 6843531-May-09 22:03
memberMember 6843531-May-09 22:03 
Questionerror message: you do not have permission to use the bulk load statement Pin
Member 475805515-Apr-09 15:31
memberMember 475805515-Apr-09 15:31 
AnswerRe: error message: you do not have permission to use the bulk load statement Pin
Adrian Pasik15-Apr-09 18:26
memberAdrian Pasik15-Apr-09 18:26 
GeneralRe: error message: you do not have permission to use the bulk load statement Pin
Member 475805515-Apr-09 18:34
memberMember 475805515-Apr-09 18:34 
GeneralBrawo Pin
PL0126-Mar-09 23:53
memberPL0126-Mar-09 23:53 
QuestionWhat about restore DB Pin
Falcon20-Mar-09 4:56
memberFalcon20-Mar-09 4:56 
AnswerRe: What about restore DB Pin
Adrian Pasik20-Mar-09 5:10
memberAdrian Pasik20-Mar-09 5:10 
GeneralRe: What about restore DB Pin
Falcon22-Mar-09 23:37
memberFalcon22-Mar-09 23:37 
GeneralRe: What about restore DB Pin
Adrian Pasik23-Mar-09 5:40
memberAdrian Pasik23-Mar-09 5:40 
AnswerRe: What about restore DB Pin
A7mad_2-Mar-12 23:27
memberA7mad_2-Mar-12 23:27 
GeneralJIT Pin
Jamal Alqabandi16-Mar-09 17:16
memberJamal Alqabandi16-Mar-09 17:16 
GeneralNeat idea Pin
AWdrius12-Mar-09 23:25
memberAWdrius12-Mar-09 23:25 
GeneralWithout temp table Pin
mkornreder12-Mar-09 5:25
membermkornreder12-Mar-09 5:25 
GeneralRe: Without temp table Pin
Adrian Pasik13-Mar-09 5:18
memberAdrian Pasik13-Mar-09 5:18 
GeneralBackup size limit Pin
alexruf8-Mar-09 22:36
memberalexruf8-Mar-09 22:36 
GeneralRe: Backup size limit Pin
Adrian Pasik8-Mar-09 23:37
memberAdrian Pasik8-Mar-09 23:37 
GeneralRe: Backup size limit Pin
RNOVAK16-Aug-11 8:13
memberRNOVAK16-Aug-11 8:13 
GeneralNever thought about that Pin
zlezj8-Mar-09 5:15
memberzlezj8-Mar-09 5:15 
GeneralRe: Never thought about that Pin
Adrian Pasik8-Mar-09 23:37
memberAdrian Pasik8-Mar-09 23:37 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 8 Mar 2009
Article Copyright 2009 by Adrian Pasik
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid