hi, we are using sql2005 db from a third party webserver for our online intranet application, every time i wish to back up the file i have left without any choice by clearing the data in my local database and import the data from the webesrver. is there any alternative methods (i have tried back up option in the sql 2005 but i am unable to set my local system drive as the location ) any ideas.
Answer by Kristen ·
You may be able to set a path to a local drive in the backup, but the performance may be such that it effects the database as a whole (what if it takes hours and no other, local, backups run and you then have a situation that needs a restore?)
I also worry that the COMMs is relatively fragile for such solutions, so once set up and automated there is a risk that you are not well failures of when it fails, and thus you may be in bad shape when a restore is actually needed.
My preference is to take a local disk backup on the server, and then move / copy that file elsewhere. For example, use the BACKUP command and then, say, FTP to transfer the file locally.
Answer by mrdenny ·
You won't be able to run a backup from the hosted server to your desktop. Your personal filewall will prevent it, and your ISP is almost guaranteed to be blocking those inbound network ports.
If the SQL Server and Web Server are different servers: If you are on a shared SQL Server, you probably don't have the rights to backup the database yourself. If you do, and the account which runs the SQL Server has admin rights to the web server and you know the actual drive letter and path to your website, you can backup the database to the web server's hard drive then download the backup from your website.
To do this you'll have to backup to the network share of the website. In the sample code (this will only work via T/SQL, not via the GUI) I use the E Drive as the drive that your website is on. This may or may not be the case.
BACKUP DATABASE YourDatabase TO DISK='\\WebServerName\e$\Path\To\Your\Website\Filename.bak'
If your SQL Server and Web Server are on the same server: They just backup the database to the folder which contains your website, then download the file from your site. This can be done with code (see below) or done via the GUI. Again in the sample I'm using the E Drive, you'll need to adjust as needed.
BACKUP DATABASE YourDatabase TO DISK='E:\Path\To\Your\Website\Filename.bak'
Now odds are you don't actually have the rights to backup the SQL Server database. You will probably have to have the hosting company do this for you and place the backup file on your website. Some hosts will do this for you, some won't. Some give you a way to do this via there portal, some require that you submit a ticket to them.