Solved

Move Files To Dropbox

Posted on 2014-11-15
7
202 Views
Last Modified: 2014-11-18
Once a week I want to move my sql server back ups to dropbox (after deleting the old backups on dropbox). then delete my local backups.  That way I will have backups going back two weeks.  If I run this script every monday morning, will it do what I want?  (Also, is two weeks of back ups considered "safe" by industry standards?)

'If WScript.Arguments.length = 0 Then
'	Set Shell = CreateObject("Shell.Application")
  
	'Pass a bogus argument with leading blank space, say [ uac]
'	Shell.ShellExecute "wscript.exe", Chr(34) & WScript.ScriptFullName & Chr(34) & " uac", "", "runas", 1
'Else
	'Set runtime variables
	Today			= Replace(Date(), "/", "-")
	SQLBakPath		= "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup"
	DropBoxPath		= "C:\Users\Administrator\Dropbox\data_backup"
	Set FSO			= CreateObject("Scripting.FileSystemObject")
	Set SQLBakDir		= FSO.GetFolder(SQLBakPath)
	Set Shell		= WScript.CreateObject("WScript.Shell")

        'delete all files in the dropbox directory
	For Each Folder in DropBoxPath
		For Each File in Folder.Files
			File.DeleteFile
		Next
        Next

	'Move all the SQL backup files to the dropbox directory
	For Each Folder in SQLBakDir
		Set DestFolder = DropBoxPath & "\" & Folder

		For Each File in Folder.Files
			Set DestFile = DestFolder & "\" & File.Name
			FSO.MoveFile File.Path, DestFile
		Next
	Next

        'delete all files in the dropbox directory
	For Each Folder in SQLBakDir
		For Each File in Folder.Files
			File.DeleteFile
		Next
        Next

	'Unset object variables and quit
	Set Shell		= Nothing
	Set SQLBakDir		= Nothing
	Set FSO			= Nothing
'End If
WScript.Quit()

Open in new window

0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 250 total points
ID: 40445243
It looks like it should do. However, you might want to look into putting a delay into the loop moving files into the Dropbox, as it'll be maybe a very long time for it to sync each file up to the Cloud. I'd also delay deleting the files in the backup directory until they're really there in the Dropbox directory.
*   You can check that with filesize, and also by checking if they're still locked locally (they should be when they're being moved).
*   You can also check by invoking a SQL Server job to read the various .bak files and return an appropriate result (something like "Yes" if they all open ok). You don't need to read the whole bak file - just the directory info - there's a SQL command for it.

BTW, your comment in line 32 should say "backup", not "dropbox" !

I'd also suggest that you check that your backups are as compressed as possible, for the same reason. I believe that Red Gate's backup compression is still better than Microsoft's, so you might want to consider their tool.

 hth

Mike
0
 
LVL 43

Assisted Solution

by:Steve Knight
Steve Knight earned 250 total points
ID: 40445378
Also consider that Dropbox typically doesn't sync to the net. unless the user is logged in... so at basic level if someone logs the console off or logs off from Rdp session or power failure means rebooted and not yet logged in... then not syncing when you think it is.  I'd keep those two weeks worth or more of backups AND sync them to Dropbox too frankly even if that means providing more local disc space.

In terms of 2 weeks.... It depends. Do you run a procedure at month end on the data that you might not notice had corrupted a table till the following month end ran... couple if weeks worth fine for DR if you need to restore after failure but what if you need to get back tear old data that was deleted by someone doing dodgy Sql statement who had too much rights.

you know your data and how likely all that is or not!

Steve
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40445954
Ditto what Steve says too! - Mike
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

Author Closing Comment

by:Bob Schneider
ID: 40446448
Some great points.  I am going to comment out the delete of the local files until I see how it is working.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40446453
Before re-instating the deletion, try replacing it with a move (to another local directory). That way you won't lose anything if it hasn't finished syncing ...
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40446457
Agreed there. Even if it means hanging g a 1Tb USB  drive off the back might mean you could have many multiple backups then while keeping recent ones on Dropbox as DR.
0
 

Author Comment

by:Bob Schneider
ID: 40450643
Got it.  Thanks so much.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question