I am just getting started with MS SQL and am having to learn from scratch. I have a DB running with an ISP which I need to back up to one of my local machines. I have installed MS SQL Server 2005 and also MS SQL Server Management Studio Express. Using this, I have opened a new DB on the local machine and then accessed the DB on the remote server. If I then right click on the remote DB file and select Tasks\Backup, I see the backup options screen. The problem is that the destination for the backup is automatically set to a folder on the remote server and I cannot figure out how to change this so that I can download to the local machine. If I click on the 'Add' button to create a new backup path, it will only offer me a view of the remote machine, not the local file structure.
The solution is probably pretty simple, but after several hours of experimentation and searching for solutions, I still can't come up with the answer. Can anyone offer some guidance please (in idiot language - I'm new to this).
When you are looking at the remote database you want to backup, is there a Remove button right below the Add button? If so, click the Remove button.
Then on the local server you'll need to Share out your backup folder. So if your backup folder is J:\sqlbackups, you'll need to share that folder with at least Change permissions at the share level, and Write permisisons at the NTFS folder level.
Once you have done that go back to your backup screen, and click Add, when the window pops up, instead of browsing for a location, type in the share location you just created, i.e. \\mylocalserver\sqlbackups
You also have the option of not sharing out your local backup folder and just typing in \\mylocalserver\j$\sqlbackups, but that is not usually a recommended way of doing it.
When you are backing up a remote database, it needs to be directed where it should be backed up to on your local server via a network share. Otherwise it only understands the file structure on the server it is running on, because it is on a separate physical server from where you are trying to create the backup.
No comments:
Post a Comment