June 16, 2004 at 4:39 pm
I’m trying to come up with a solution to provide a periodic secure backup service for our clients’ MSDE databases that we would house on SQL Server. What’s the best way to backup an MSDE database over the internet? Would you recommend replication, log shipping, something else? Can you also explain pros and cons of these strategies, as I’m a newbie?
June 17, 2004 at 5:21 am
I guess it depends on a variety of factors:
There are many ways to address this. A simple solution may be creating daily backups of the client db to a FTP accessible directory on their end, then creating periodic jobs to retrieve that backup file. FTP is simple, and provides authentication and even encryption.
Adam
June 17, 2004 at 2:17 pm
Thanks Adam! Here are some more details…
Is your FTP suggestion something we can do with SQL Server or would this require some kind of script? We would also like the ability to query the backup databases for research purposes.
June 17, 2004 at 2:53 pm
Seems like you should be able to back the db up locally and set up an ftp job (perhaps using the xp_cmdshell procedure) to send it over to you. You would still have a local copy so it could be used if needed. That would allow a recovery to be completed in much less than 12 hours.
June 18, 2004 at 5:14 am
Joe,
If you need encryption for the transfer (which may be excessive, but only you know your security requirements), then you have to use Secure http://FTP. I'm familiar with the CuteFTP.com suite of products. You can specify directories and permissions for each customer, and all access is authenticated and logged.
You may run into issues with the size of the backup. In my experience, file transfers greater than 500mb have a tendency to be unstable. You may want to considering using WinRAR or some other archiving and chunking tool.
Both WinRAR and SecureFTP have scripting capability, so you can build a batch or script to execute each task automatically, and call the job from within SQL Agent or Windows Scheduler.
Of course, this is just one method. I'd be glad to see other solutions to this problem.
As Matthew stated, once you have the backup, you can have a functional database up and running within minutes, both for backup and reporting.
AT
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply