Using Smo.Backup to backup SQL Server database to string

Question

I'm trying to make a little app that would help me making my server backup. That app would run on my home PC so the main goal is to be able to connect to the external server, backup the selected database, dump the backup content to a string or something so I could write it on my PC disk and not the server's disk.

I did that which works to write on the server's disk, but I'd like to be able to write on my PC's disk the backup's result.

private bool BackupDatabase()
{
    try
    {
        // Filename
        string sFileName = string.Format("{0}\\{1}.bak", _sWhereToBackup, DatabaseName);

        // Connection
        string sConnectionString = String.Format(
            "Data Source=tcp:{0};Initial Catalog={1};User ID={2};Pwd={3};",
            DatabaseHost, DatabaseName, DatabaseUsername, DatabasePassword);

        SqlConnection oSqlConnection = new SqlConnection(sConnectionString);
        Server oServer = new Server(new ServerConnection(oSqlConnection));

        // Backup
        Backup backup = new Backup();
        backup.Action = BackupActionType.Database;
        backup.Database = DatabaseName;
        backup.Incremental = false;
        backup.Initialize = true;
        backup.LogTruncation = BackupTruncateLogType.Truncate;

        // Backup Device
        BackupDeviceItem backupItemDevice = new BackupDeviceItem(sFileName, DeviceType.File);
        backup.Devices.Add(backupItemDevice);

        // Start Backup
        backup.SqlBackup(oServer);
    }
    catch (Exception ex)
    {
        throw ex;
    }

    return false;
}

Thanks so much!

How you will connect to that database from your home. VPN ? if yes then simple c# code to copy file from server to your pc — Surjit Samra
– Surjit Samra, Commented Nov 20, 2011 at 10:53

Adam Tuliper · Accepted Answer · 2011-11-20 07:08:33Z

1

This is going to get a bit hacky because you need to either

call sql functions to read the file on the server and return as a binary array to you and then convert back to a file. this will require proper permissions as the account you are running under to access the file on the server's drive.

you can use t-sql or a bit more 'advanced' .net code t-sql can be seen in this great sql injection guide http://www.blackhat.com/presentations/bh-europe-09/Guimaraes/Blackhat-europe-09-Damele-SQLInjection-slides.pdf

or .net: http://www.mssqltips.com/sqlservertip/2349/read-and-write-binary-files-with-the-sql-server-clr/

map a file location (i.e. access a share/drive/ipc connection) over the network
have the server ftp the files to a location

Which sounds likely in your scenario?

answered Nov 20, 2011 at 7:08

Adam Tuliper

30.2k4 gold badges56 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brandon Moore Over a year ago

Upvote for covering a range of possibilities, though I think the 1st option is probably the not something anyone who is authorized to backup to their home machine would ever need to bother with. I think that option is normally going to be reserved for people attempting to do something they shouldn't.

Adam Tuliper Over a year ago

@BrandonMoore thanks. I disagree a small bit on the point as the OP mentioned about returning the backup content as a string. Because of that I have to assume the lack of a simple file share wanted would tend to imply he wants something like that (for whatever reason). Personally I would just of course copy the files on my home network, use IPSEC, etc. but who knows the reasons here : )

Brandon Moore Over a year ago

Oh I agree it's the most correct answer to the question that was asked so I don't think there's any disagreement on that :)

Brandon Moore · Accepted Answer · 2011-11-20 07:17:46Z

I take it that you're not networked to the server? Ideally you could back it up directly to your machine using a network address but I'm guessing you would have already thought of that.

Going the route you're thinking of is going to require you to have permissions that you would not normally want to have opened up to the someone over the internet, and will be much more trouble to program than simply setting up a second process to move the file somewhere accessible to you.

Adams suggestion to have the server ftp the files is a good one. Alternatively, you may find that using DropBox (www.dropbox.com) might be the path of least resistance. Then you could just back the database up to a folder setup with dropbox and it will automatically be copied to any folder you setup on your machine to be synchronized with it.

Nisd · Accepted Answer · 2011-11-20 21:54:10Z

0

You should look into DAC, it is mainly made for the SQL Server in Azure, but will work with a SQL Server 2008 R2 too.

http://sqldacexamples.codeplex.com/

answered Nov 20, 2011 at 21:54

Nisd

1,15311 silver badges22 bronze badges

Collectives™ on Stack Overflow

Using Smo.Backup to backup SQL Server database to string

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related