• Skip to main content
  • Skip to primary sidebar

Technical Notes Of
Ehi Kioya

Technical Notes Of Ehi Kioya

  • Forums
  • About
  • Contact
MENUMENU
  • Blog Home
  • AWS, Azure, Cloud
  • Backend (Server-Side)
  • Frontend (Client-Side)
  • SharePoint
  • Tools & Resources
    • CM/IN Ruler
    • URL Decoder
    • Text Hasher
    • Word Count
    • IP Lookup
  • Linux & Servers
  • Zero Code Tech
  • WordPress
  • Musings
  • More
    Categories
    • Cloud
    • Server-Side
    • Front-End
    • SharePoint
    • Tools
    • Linux
    • Zero Code
    • WordPress
    • Musings
Home » C# » Backup SQL Server Database To Dropbox in C#

Backup SQL Server Database To Dropbox in C#

By Ehi Kioya 10 Comments

SQL Server 2008 allows for the creation of local backups. However, these backups must really be “local” in the sense that they can only be created on the machine where the SQL Server instance is installed. The whole idea behind database backups is about keeping a copy of data in case of Server failure or some other problem. So storing the backed up data on the same machine is definitely not a good idea.

A good idea would be to take regularly backups and store these backups in a remote machine and/or online using cloud computing. SQL Server management Studio however, has no built-in feature to automate this. However, I managed to find a way around this using Dropbox API. Here are the summarized steps to backup the database to dropbox:

I first create a program that runs from a remote computer and accesses the SQL Server database. Now since a regular backup is only permitted on the Server, I simply generate a script of the database and set options to make this script hold both schema and data. The code for this step is shown below. PCP is the database name.

public static void GenerateScript(string path)
{
     DataSet ds = new DataSet();
     string SQLCommand = "use PCP\n";
     SQLCommand += "select * from sys.Tables";
     SqlDataAdapter Adapter = new SqlDataAdapter(SQLCommand, DatabaseIO.conn);
     Adapter.Fill(ds);
     int x = ds.Tables[0].Rows.Count;
     string[] myTables = new string[x];
     for (int a = 0; a < x; a++)
     {
          myTables[a] = ds.Tables[0].Rows[a][0].ToString();
     }
     Server srv = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection("MYSERVERNAME"));
     Database db = srv.Databases["PCP"];
     ScriptingOptions options = new ScriptingOptions();
     options.ScriptData = true;
     options.ScriptDrops = false;
     path += "\\script(" + GlobalVariables.currentDateAndTime + ").sql";
     if (File.Exists(path))
     {
          File.Delete(path);
     }
     options.FileName = path;
     options.EnforceScriptingOptions = true;
     options.ScriptSchema = true;
     options.IncludeHeaders = true;
     options.AppendToFile = true;
     foreach (string tbl in myTables)
     {
          db.Tables[tbl].EnumScript(options);
     }
}

Now we have a copy of the database in script format. All that remains is to create a “Cloud” copy. To do this, I use Dropbox Service and API. See the code below.

NOTE: My credentials have been removed.

public static void SendToDropbox(string path)
{
     DropBoxCredentials credentials = new DropBoxCredentials();
     credentials.ConsumerKey = "";
     credentials.ConsumerSecret = "";
     credentials.UserName = "";
     credentials.Password = "";
     DropBoxConfiguration configuration = DropBoxConfiguration.GetStandardConfiguration();
     CloudStorage storage = new CloudStorage();
     ICloudStorageAccessToken StorageAccessToken = storage.Open(configuration, credentials);
     path += "\\script(" + GlobalVariables.currentDateAndTime + ").sql";
     storage.UploadFile(path, "/Backup", "script(" + GlobalVariables.currentDateAndTime + ").sql");
     if (storage.IsOpened)
     {
          storage.Close();
     }
}

The result is that a script holding both the schema and data of the SQL Server database is saved on the computer used to run the application and also saved to Dropbox!

Found this article valuable? Want to show your appreciation? Here are some options:

  1. Spread the word! Use these buttons to share this link on your favorite social media sites.
  2. Help me share this on . . .

    • Facebook
    • Twitter
    • LinkedIn
    • Reddit
    • Tumblr
    • Pinterest
    • Pocket
    • Telegram
    • WhatsApp
    • Skype
  3. Sign up to join my audience and receive email notifications when I publish new content.
  4. Contribute by adding a comment using the comments section below.
  5. Follow me on Twitter, LinkedIn, and Facebook.

Related

Filed Under: Backend (Server-Side), C#, News Tagged With: Database, Dropbox

About Ehi Kioya

I am a Toronto-based Software Engineer. I run this website as part hobby and part business.

To share your thoughts or get help with any of my posts, please drop a comment at the appropriate link.

You can contact me using the form on this page. I'm also on Twitter, LinkedIn, and Facebook.

Reader Interactions

Comments

  1. Mark R. says

    January 17, 2013 at 11:05 am

    Try using SQL Backup Master. It can back up SQL Server databases to Dropbox (and other cloud storage services) with no programming required. And it’s free.

    http://www.sqlbackupmaster.com/

    Reply
    • Ehi Kioya says

      January 24, 2013 at 4:58 pm

      Thanks Mark. I had never heard of this and I’ll probably give it a try someday. The basic edition looks great and offers Dropbox backup for free.

      I came up with the method described here in the early days of Dropbox when automated solutions did not exist. Besides, persons who prefer to be in full control of the backup process will still want to write the code themselves.

      Reply
  2. Alexey says

    February 21, 2013 at 12:22 pm

    Hi,

    Right, it’s a good solution for many companies to keep backups on a cloud storage like Dropbox (or Google Drive or Amazon S3). I think it should has ability to compress backup images before post them to a cloud.
    SQLBackupAndFTP has this functionality too. Also there is ability to backup databases using sql scripting like in your example above, and compress them with embedded archiver or with 7-zip software.

    http://sqlbackupandftp.com/

    Reply
    • Ehi Kioya says

      February 22, 2013 at 5:05 pm

      Thanks for the link Alexey.

      For those with Microsoft Certifications, you might be interested in this offer I found while browsing the MCP page:

      Exclusive offer: SQLBackupAndFTP Professional You are eligible to download SQLBackupAndFTP Professional ($69 value) for free if you are currently holding the following certifications: MCDBA, MCITP, MCSE, MCSM, MCSD, MCT, MCM, and MCA. Please contact us with a confirmation of your certification and we will email you a license key. This offer expires on May 31, 2013.

      I might take advantage of this myself. Just not sure how much effort will be required in the ?confirmation? process.

      Reply
      • Alexey says

        March 5, 2013 at 12:17 pm

        Hi Ehikioya,

        It’s a really nice offer. Thank you for the info!

        Regards,
        Alexey.

        Reply
  3. Shawnee Heinen says

    November 26, 2013 at 1:09 pm

    Hehe dropbox, nice nice but why not try COPY? Free 15GB space and you can get unlimited! bonus space from referrals: https://copy.com?r=7IdxUA

    Reply
  4. prakash says

    April 6, 2014 at 1:53 pm

    Hi Ehikioya,

    It’s nice article. Thanks for sharing this information.

    As in your code you are using “DropBoxCredentials” class for upload the file into dropbox. Can you please tell me how can i get this dll/reference information?

    Any help would be greatly appreciated

    Thanks,
    Prakash

    Reply
  5. Igor says

    May 27, 2014 at 3:55 pm

    Hi student,
    Your programming looks pretty good. I’d try to throw away DataAdapter and use DataReader instead. DataTable has a method load which loads the contents of the reader. It is much cleaner (Reader is much more predictable than Adapter)
    Your prof 🙂

    Reply
    • Ehi Kioya says

      May 27, 2014 at 4:04 pm

      Thanks for the comment Prof. Your point is noted and APPRECIATED!

      This particular post is a little old and needs updating. I plan to update it soon.

      I consider it a big honor to have your comment on here. Thanks again.

      Reply
  6. Pedro gort Aramillo says

    July 27, 2019 at 6:42 am

    Thanks for all you help with out you help and your good Hart i can not even make ,please enjoy me in this journey I want you to be a part of all this thanks to all

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

26,169
Followers
Follow
30,000
Connections
Connect
14,641
Page Fans
Like

POPULAR   FORUM   TOPICS

  • How to find the title of a song without knowing the lyrics
  • How To Change Or Remove The WordPress Login Error Message
  • The Art of Exploratory Data Analysis (Part 1)
  • Welcome Message
  • Getting Started with SQL: A Beginners Guide to Databases
  • Replacing The Default SQLite Database With PostgreSQL In Django
  • How To Create A Contact Form For Sending Emails In Laravel
  • Shared Hosting vs VPS – Which Should You Choose?
  • Data Acquisition Strategies – A Data Scientist’s Perspective
  • Top Business Intelligence Tools in Demand
  • Recently   Popular   Posts   &   Pages
  • Actual Size Online Ruler Actual Size Online Ruler
    I created this page to measure your screen resolution and produce an online ruler of actual size. It's powered with JavaScript and HTML5.
  • Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library
    This article explains how to bypass the single user remote desktop connection restriction on Windows 10 by using the RDP wrapper library.
  • WordPress Password Hash Generator WordPress Password Hash Generator
    With this WordPress Password Hash Generator, you can convert a password to its hash, and then set a new password directly in the database.
  • Forums
  • About
  • Contact

© 2021   ·   Ehi Kioya   ·   All Rights Reserved
Privacy Policy