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!
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/
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.
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/
Thanks for the link Alexey.
For those with Microsoft Certifications, you might be interested in this offer I found while browsing the MCP page:
I might take advantage of this myself. Just not sure how much effort will be required in the ?confirmation? process.
Hi Ehikioya,
It’s a really nice offer. Thank you for the info!
Regards,
Alexey.
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
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
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 🙂
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.
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