MS SQL 2008 backup over internet

Question

MS SQL 2008 backup over internet

mike 82903

SSC Enthusiast

Points: 107
More actions
March 2, 2010 at 4:01 am

#211755

Hi Forum,
I'm trying to find out if it is possible to backup an SQL 2008 database over the internet using server management studio or any 3rd party utility. I need to ensure that I obtain a full backup of the database with data and table structure etc. To date, I have only been able to backup the database to the local machine our database it is hosted on and unfortunately I am unable to gain FTP access to this file.
I've tried using the import and export 32 bit utility and although this completes successfully there is a huge difference in size between the two DB's. Our live online database is around 650MB and after the import/export process is complete the new database it has imported to on my machine is only 35MB so there is obviously something missing here. I thought this utility would create a copy database that I could then backup but I am obviously missing something
Comments welcome and thanks in advance, your help is appreciated.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Backing up to a remote location is going to be extremely slow.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mike 82903 SSC Enthusiast Points: 107 More actions · Answer 2

Thanks but I presume your comment is to get your posting level up. Not much help with topic but yes Internet can be slow. Will depend on connection type, physical location, number of router hops between client/server, type of data encryption, latency......just a few things that my affect your speed.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thank you for your feedback.

I will accept the advice of anyone on this issue.

But if you perform a backup to a disk that is not on the same Network Segment than you will experience latency.

Respectfully have you every tried Lightspeed or one of it's competitors backup and restore utilities?

Regards

Welsh "DIck" Corgi

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Do you have a SAN?

Can you back up to the SAN or local Disk storage. THen you can perform tape backups./

Backing up across the network is going to be extemly slow...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

First, the remote location would have to be accessable over the internet.

Second, you would have to grant proper permisions to the account that SQL Server is running under assuming it is running under a domain account. If it is running under Local System, you won't be able to as it won't have access to network resources.

Third, even if you could, unless you have a very solid internet connection chances are the backup may fail. Backups are sensitive to network hiccups and delays causing them to fail.

I'd contact your hosting provider about options to obtain current backups of your database for your development purposes.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Very well stated Lynn.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

YOu should post Sql server 2008 related question on sql server 2008 tabs.this is for sql server 2005 😛

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

mike 82903 SSC Enthusiast Points: 107 More actions · Answer 8

Thanks for all your replies.

I've now managed to duplicate this database using the import/export wizard. I've since found out that the huge difference in size (as stated in my original post) between the two DB's was down to growth contraints as specified in the backup routine.

Using the import/export wizard I was able to extract the data from hosted online database and create a new database locally within SQL 2008 server on my local machine. Using a 3rd party utility I was able to compare the records between the DB's and confirm everything matched.

Thanks again for your comments.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

if you want internet backups then look into some of the cloud solutions from i365 or others

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Mike,

I'm sorry about the initial reply. I was not my intention to offend you and I should have read your question better and engaged my brain before I opened my mouth. 🙂

I think the flea infestation is getting to me.

I should have points deducted for that reply. :w00t:

I never performed a backup across the internet but I have backed up to a remote location and I experienced some of the problems that Lynn stated in her post.

Do you have enough local or SAN Storage to perform a local backup?

I will probably take a lot of heat for suggesting this :hehe:

If you can backup via local or SAN (performance, reliability, etc) then perhaps you could do the following.

Compress your data.

Coordinate and setup an FTP Site.

Keep in mind security. (Encryption)

This may be the best solution but it will probably generate some discussion.:w00t:

I hope that everything works out for you.

Regards,

Kent

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

GTR SSCrazy Eights Points: 8007 More actions · Answer 11

mike 82903 (3/5/2010)
Thanks for all your replies.
I've now managed to duplicate this database using the import/export wizard. I've since found out that the huge difference in size (as stated in my original post) between the two DB's was down to growth contraints as specified in the backup routine.
Using the import/export wizard I was able to extract the data from hosted online database and create a new database locally within SQL 2008 server on my local machine. Using a 3rd party utility I was able to compare the records between the DB's and confirm everything matched.
Thanks again for your comments.

Mike,

The reason sizes of two databases did not match because, you are only copying data using Import\export not schema and other objects. If you want the exact copy of database i would recommend you to use backup and restore method.

EnjoY!