SQL Server - Remote Database Backup

Asked By Yuri Baijnath
23-Sep-09 06:48 AM
Hi guys.

I have been tasked with creating a database backup solution,
which sends core data to an offsite location. The data will be sent via a GPRS connection linking the various sites.
I have to backup three sql server databases (2 sites running sql server 2000 and the other sql server 2005) at an offsite location running a server running  SQL server 2005. 

I have to do this without making any changes to table structures etc.
The GPRS connections are not the most reliable and have been know to fail from time to time.

Any ideas or suggestions on how i go about doing this will be greatly appreciated.

Thanks in advance
  Jonathan VH replied to Yuri Baijnath
23-Sep-09 08:00 AM

Don't even consider using SQL Server's backup to create a file on a network share.  This will typically lead to problems if one does it over a LAN, much less a flaky WAN.  Problems like finding you cannot restore because one bit out of billions got flipped during transmission.  Instead make SQL Server backups to files on the SQL Server's local (or SAN) storage, then copy those files over the network using something like Robocopy with all the error-checking you can muster. 

You can use SQL Server Agent jobs (which can include command line, PowerShell, etc. steps) to do this and either schedule push jobs or invoke it remotely to pull.  You can also execute SQL Server commands like BACKUP from T-SQL or smo.  SQL Server Books OnLine (BOL) has complete documentation on al this.  If you don't have a copy (it comes with SQL Server), you can download it:

http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

Re  Re

23-Sep-09 11:55 AM
HI

if you are going to do by the program in .net, then here is way

http://www.codeproject.com/KB/database/BackupUtility.aspx

thank you

Yeah  Yeah

24-Sep-09 12:58 AM
end of post
Ignore my previous reply. Submitted the reply too quick,  Ignore my previous reply. Submitted the reply too quick,
24-Sep-09 01:00 AM
You can try SQL DMO as expalined http://www.codeproject.com/KB/dotnet/SQL_Server_backup.aspx

Or even look at Microsoft Database Publishing Wizard. It can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en.

I am expecting your help  I am expecting your help
24-Sep-09 03:08 AM
I have been watching your brilliant suggestions in many questions. Could you please give your valuable suggestions on http://www.eggheadcafe.com/community/aspnet/66/10120086/inconsistency-in-internet.aspx thread.

Thanks.
Create New Account
help
SQL server 2000 SQL Server Does Microsoft still support SQL server 2000? SQL Server New Users Discussions SQL Server (1) SQL Server Books Online (1) SQL server 2000 (1
MSDE on Windows 2003 R2 box, new DL385G6 - Install Fails during SQL Services SQL Server I have been finding that I am having trouble with the Crystal Reports Server XI installation failing when it is dealing with SQL. So, as a thought and in case there was something wrong with my SQL portion of the isntall. I thought ok, I will try installing the actual MSDE application direct from Microsoft. So, I downloaded the MSDE for SQL 2000 (which is msde2000a.exe), set my switches and off to the races. It quit seconds left to the installation and bombed with the same errors as the Crystal Reports Server install. The error is the same whether I try to install MSDE by itself or
strawberry perl and sql server SQL Server I need to connect to sql server from strawberry perl. Is anyone aware of any free driver for this. thanks. SQL Server Discussions SQL Server 2005 (1) SQL Server 2000 (1) SQL Express (1) SQL Server (1
server SQL Server Do I have to set up a sql server database on a sql server? Or can I do this on any server SQL Server Setup Discussions SQL Server (1) CREATE DATABASE (1) Databases (1) Database (1) Create
Conditional compilation [SQL Server] SQL Server Does SQL Server support conditional compilation? - -Sunny SQL Server Discussions SQL Server (1) Preprocessor (1) Sunny.mohan (1) Ifdef (1) Can you define what