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!