Using SQL Sever Management Studio 2014 Express, I can connect to the web Hosts SQL 2008 R2 server, and select the database I wish to backup, then right click | Tasks | Backup. I check copy-only backup, I wish to save to Disk, and am offered an unsuitable path when I click add - but even when using that , or entering a destination as simple as C:\ the OK button remains greyed out. Clicking the ... button beside it results in an error message:
[quote]Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.[/quote]
Well the C:\ destination exists, and I cannot think of any security privileges that need enabling.
Adding in the file name clears the OK button - but SSMS cannot find the path - and the browse button does not call up the browser window, but instead brings up an error alert. TBH the computer is in a bit of a mess - I did not clear out all directories and folders when I installed the latest version of SSMS - though I thought I had uninstalled everything I needed to. I suspect that the best thing to do is to uninstall everything sql related, and make sure that every sql directory is cleared so that I can install on a cleanish base.
That said, I am not clear what you meant by 'C:\ is your SQL server drive'. I am attempting t back up from the hosts SQL server to a location in a local database so that I can restore it there. AFIK, the location I should be saving to on my local computer, which does exist in Windows Explorer is
C:\Program Files\Microsoft SQL Server\MSSQL12.2014\MSSQL\Backup
If the SQL Server instance is on computer A and you are running SSMS from your local computer B, when you use C in the backup wizard, that is on computer A not your local computer.
Also I do not know if it is still true but in the past you could not save a backup to a different computer, although I am not sure about mapped drives.
I was able to send a backup to my computer earlier this month on another computer. Oddly, I can't do now, however, as only a few fields in one row of a table have been affected since the backup, I have been able to restore the database on my local server the update the affected row manually, so that I now have a local clone of the database used for my web site. This means I can now run a local version of the site to test out changes before I make them live no the web site itself.
As the clone will reflect what is happening on the live site, I now no longer have a problem. Backups happen on the live site, but I am not able to access them myself - probably because the data base is a shared one.
I have no idea what has caused the problem - I did change outbound firewall rules on the router for port 1433 (disabling it for outbound) but I don't think that should affect matters.
I rashly accepted the post of webmaster at the AGM of a model aircraft association at is AGM (no one else would volunteer)- and found somewhat to my surprise a website, with no CMS, and based on asp.net. I am retired - and it is a few years since I coded a couple of asp.net sites. Fortunately, this one is back in my era - uses asp.net 3.5, and it is information only, with no user input. I may change that - but if I do I will make sure I have better control over database backup. Talking of which, the site one is used to supply web page content, but it is not relational; all the tables are unrelated tables - flat files would have worked just as well. For the meantime, I can now see that I have no immediate need to transfer further backups of the database to my computer. When I do, I shall change web packages/hosts to make sure that the facility is readily available.
In the long term, I will probably switch the site to a CMS, and encourage user input - but in the meantime I have quite a lot of tidying up to do.
Thanks djj55 for the helpful discussion I received on this thread, it was appreciated.
Just a thought but for "adhoc" restore onto your computer perhaps you could just download/"copy" a backup from the server to your PC and then restore it locally?
To make a directory and use it as the backup directory.
Also, you need to check whether you SQL service account has the w permission to the backup directory.