Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kevinmcgillivray
Starting Member
2 Posts |
 Posted - 2006-06-08 : 21:41:35
|
| Hi, guys. I've spent some time reading the forums looking for an solution to this problem so I apologize if has already been covered...I host a number of SQL Server databases on my ISP's servers. I don't have any physical access to the boxes and they're not accessible via UNC. I am, however, able to log in to the servers using Enterprise Manager and my login has DBO privileges to my databases but little or no access to anything else.I would like to backup my remote databases to a server on my local network on a nightly basis. Ideally, I would be able to keep a running set of the seven most recent backups and just roll through them as they age.I can't imagine I'm the first person to run into this issue so I'm hoping someone out there has a solution they've developed and are happy with. Any suggestions?Thanks!Kevin |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 02:15:44
|
| I think you options are going to be pretty much limited to:1) FTP the backup files down - insecure unless the ISP provides SSL-style FTP2) If your backup files are visible from, say, a web site at the ISP you could manipulate the files from the web site. For example, in the past I have set up a task on the web site that ZIP's, PGP'd and copied the resulting file to the website so it could be downloaded with HTTP3) You might also be able to get the Hosting server to "push" the backup file to your machine; but you'd need access to xp_cmdshell, which I very much doubt you have!Kristen |
 |
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-06-13 : 02:33:27
|
| So in short, you can't yourself make a backup on a remote server to your local machine directly, you will need (initial) assistance from the ISP in one of the ways Kristen mentions.-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those. |
|
|
|
kevinmcgillivray
Starting Member
2 Posts |
Posted - 2006-06-13 : 10:05:55
|
| Thanks for the suggestions. I've talked to my ISP and all they do for their clients (as far as backup goes) is provide us DBO access with Enterprise Manager. They don't do any of the things that you suggested and I'm not able to switch ISPs.I'm okay if the solution here doesn't actually use the "backup" feature in Enterprise Manager/SQL Server. The most important thing is that I get a copy of the data down to my server in some form or another, that I have seven days of data on my local machine and that it's automated. I'm willing to use DTS or whatever means achieves this and it doesn't have to produce an actual "backup" file. I just don't know enough about what features could be applied to this problem or how to automate it to get this done.Thanks again for your help.Kevin |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 10:53:59
|
| Sounds like your best bet may be to create an identical database locally, and then use DTS to copy the data to your local machine.Then change ISP!Are you confident they are backing up your data to tape? (Asking for a fire-drill recovery might be a wise move)If you can get DTS to connect to the remote database as the Source, and your local database as the Target, then you should be fine.Set each table to "Pre-delete the data" and then it will suck all the data down.Note that the sequence that the tables are processed will be important if you have FKs between the tables - so you may want to drop the FKs on your local database to prevent any problems like that.Likewise the data on the Target database will already have been processed by any Triggers, so you may want to DROP any triggers on your local database to stop the data getting re-processed by the triggers.But you may not have any FKs or Triggers anyway.When you manually create the DTS for the first time there is an option to "create a job" - if you choose that you will be able to re-run the rob in the future, using a schedule if you want to. There is potentially a difference between First time and Repeat time (in terms of the pre-delete process), so make sure that it tested before you schedule it and forget all about it!Having some sort of "marker" record on the Source database will allow you to prove that the data has arrived locally - by checking if the Marker record has the latest value on the Target (local) database.Kristen |
|
|
|
|
|
|
|
|
Scheduled backup of remote database