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
6 comments:
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"
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.
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.
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.)
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:
http://blog.gralen.com/2013/04/its-like-sql-import-export-wizard-only.html
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.
Post a Comment