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