Tuesday, June 22, 2010

GoDaddy Automated SQL Backups on Shared Hosting

I've been looking for a while to find out how to do automated backups with Go-Daddy's shared hosting, which, as advertised, does not allow automated backups, and Go-Daddy does not provide the service.

To compound the issue, microsoft no longer ships their import/export suite of tools for free, and as a matter of fact, they're not available at all... unless you buy a regular SQL-Server license (or forfeit your left leg)

Introducing Microsoft's "Database Publishing Wizard"

The Database Publishing Wizard is a neat FREE little tool, with both graphical, and command-line interfaces. It uses the power of sql-transact to read your go-daddy (or any SQL datasource) and save it into an sql-transact file which you can use re-create your entire database, simply by executing it as a query.

Which is great... unless you want to automate the process, in which case a GUI is pretty useless.

Introducing the command-line feature of the application:

The following command uses an SQL connect string, to access your database, and dump its entire schema, and database table contents into a giant series of queries in a .sql text file. In this case, I've tacked the additional command:

"> C:\backupJob.log"

To the end of my command, which saves the output of the process to a text file. Very useful if you plan on running this at 3 in the morning, but still need to verify that the job actually occurred.

SqlPubWiz.exe script -C "Data Source=MyGodaddySQLDataSourceURI;Initial Catalog=MyDatabaseName;User Id=MyDSNUserId;Password=MyDSNPass;" C:\backup.sql > C:\backupJob.log


Unknown said...

Thanks! This was an older way of doing things, which still works; however I currently just run a .bat file (command line) to make a db-backup directly from the database, and copy it to google-drive to off-site backup it. To make it run on a schedule, I just run the .bat file using Windows Scheduler.

Don't take my word for it: Look up the "sqlcmd" tool first! http://msdn.microsoft.com/en-CA/library/ms162773.aspx

sqlcmd -Q "BACKUP DATABASE [databaseNameHere] TO DISK = N'C:\folderNameHere\databaseNameHere.bak' WITH NOFORMAT, INIT, NAME = N'databaseNameHere-Full Database Backup', SKIP, STATS = 10"

copy C:\folderNameHere\databaseNameHere.bak "C:\pathToGoogleDrive\folderNameHere"

Anonymous said...

Thank you for this very helpful post. I have been wrestling with it for a bit. Will you please provide an example GoDaddy database name? I am not sure how to specify the server and database name. I assume sqlcmd runs from a batch file on my local computer.

Thanks for your help.

Unknown said...

Correct. These commands are running from a local windows machine's cmd window, connecting to a local, or remote sqlserver data source. The original post was referring to a work-around that was created because GoDaddy didn't allow remote MMC (Management Console) connections to their sql data sources.

·kÅz said...

Hi, thanks for this info! Any suggestions if I'm getting a "BACKUP DATABASE permission denied error in database..." result, when trying to backup a GoDaddy DB to a local file? (I'm using the same credentials as the ASP.NET web application that uses this DB, copied directly from the web.config file.)

Unknown said...

You're not going to be able to backup a ms-sql database using management console remotely. The permission denied error is telling you that you don't have access to the database server's local file system ( MMC is trying to restore the database to it's local hard-drive ).

Rather, use the Azure Migration wizard to connect remotely to your data source and grab the data this way.

Check out my post on this topic:

ShawnM said...

Thank you for the article, Jonathan.
It's true for SQL database. I also faced the same problem when working with online data room providers that needed to guarantee scheduled data backups for their clients.