backup MSSQL database remotely?
-
Hi,
I want to backup my MSSQL database using a script, but here lies the problem: The MSSQL is on its own dedicated server seperated from the dedicated WebServer.
I tried to run a script with this sql command:
BACKUP DATABASE
TO DISK = 'drive:\path\to\file\filename'
But, of course, this won't work because the script will try to write on the MS SQL server instead of where the WebServer where my account is.
Is there anyway I can do this? I just want to have the backup saved on the folders in my account in the WebServer instead of the MSSQL server.
I don't have access to the MSSQL server at all, except connect to it remotely.
Thanks in advance!
-
You can backup to a remote drive using a UNC path, like backup database to disk = '\\webserver\share\folder\file.bak', however the backup procedure isnt' very tolerant of delays.
Instead I'd write the backup to the local MSSQL server and then copy if to the web server (and delete from the sql server if you want). Work with the Sysadmins to get this done.
One caveat, you are removing a layer that can keep your data safe by moving the data onto the webserver, which is more vulnerable to attacks and exposures. Be sure you are ok with this and that the people who own the data are as well.
-
Hi,
The thing is I Want to backup the database regulary, maybe weekly or so. I don't think the sysadmin would like me to bother him to do backup and copy it over to the webserver everytime I Want it done.
Do you think there's a way to do it using ASP scripts?
Thanks!
-
I would work with the Sysadmins and get them to schedule regular backups that would comply with your companies security policies and keep you and your data safe.
Ultimately, IF the server crashed AND it was rebuilt AND you don't have access to the databases THEY are responsible and should be willing and able to help you with your request
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I think you should ask the sysadmin (for sql server) to be solely responsible for your database backup and recovery. If there are just regular sysadmin and there is none for sysadmin for sql server, I would recommend that they set you up as a sysadmin for sql server, in which you are part of the administrators group, and you are sysadmin on sql server so you could backup, restore and do your work as needed.
-
I too would work with the sysadmin and make sure that they have daily/hourly backups as needed to keep your production data safe. I would also work with them to get a local copy of the database daily/weekly as needed. This should all be automated so that all they have to do is monitor the process and fix it as needed. Asking them to do this manually every time you need the data would be asking too much. I would certainly try to keep the SQL Backups off of the webserver! Web servers are typically the first area of attack by a hacker.
At my last place of employ I worked with my sysadmin to set up a solution where the database was backed up every night. We then created an automated system that would zip the backups with encryption, ftp the files, unzip them, restore to a special database, mask the sensitive data, and finally backup that db to a share that all the developers/testers had access to. In this way we had the ability to restore the database structure with "Sample" data every day as we needed. It took us a couple weeks to set up the process but after that is ran flawlessly for about 2 years with no human intervention. We used the FTP solution as the web farm had no access to the internal network and it was the only way to get the data unless someone physically went across town to the web farm and burned a few CD's.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply