Move Files To Dropbox

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
	'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

	'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

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

	'Unset object variables and quit
	Set Shell		= Nothing
	Set SQLBakDir		= Nothing
	Set FSO			= Nothing
'End If

Open in new window

Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve KnightIT ConsultancyCommented:
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!

Ditto what Steve says too! - Mike
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bob SchneiderCo-OwnerAuthor Commented:
Some great points.  I am going to comment out the delete of the local files until I see how it is working.
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 ...
Steve KnightIT ConsultancyCommented:
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.
Bob SchneiderCo-OwnerAuthor Commented:
Got it.  Thanks so much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.