Friday, February 24, 2012

Backup/Restore SQL DB - webhost4life

I'm working with someone right now who wants to beable to click on a link and instantly beable to restore a SQL database from his hard drive or backup a SQL database to his hard drive.

I talked to webhost4life about this (where my MSSQL databases are hosted) and this is their response:

"The only way you can backup or restore a database yourself is by going to hosting control panel. You can have your clients contact us with the hosting account member ID and the database login information so we can do a manual restore or backup for them. "

This is the only way? This is not sufficient for my client that I am working with.

I've searched the forum for this and have received mixed responses. Some say to research SQLDMO and i've even seen this for restore, which I couldn't get to work:


strSQL = "RESTORE DATABASE [WebReqs] FROM DISK = N'C:\Inetpub\wwwroot\Administration\Database\WebReqsVanilla.bak' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE , MOVE N'WebReqs_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\WebReqs_data.mdf', MOVE N'WebReqs_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\WebReqs_log.ldf'"

I can't seem to get anything to work and webhost4life says it's not possible. Is there any other way??
TIAIs DTS (SQL Server Tools) working with webhost4life?|||I've successfully backed up my webhost4life database using DTS/Enterprise Manager. I don't actually do a backup though, I just pull down all the objects (do an object transfer). This will back up all the tables, sprocs, views and data, which for most web database scenerios is sufficient.

A normal Backup and Restore require sysadmin level permissions, which they are certainly not going to give you on a shared database server. I think there is a way to give that level of permission at the database level, but even if they did you would not be able to tell the remote server where you wanted the backup file (the location). In other words, it would not backup to your local computer where you could get at it.

cs|||The Intro to Webmatrix book has some scripts included for uploading/downloading table schema and data from MSDE/SQL.

They must be here on the site somewhere as well.

In the book, there's a disclaimer about security, you need remove the pages from the host server after you use them.

No comments:

Post a Comment