Database Backup with Transact-SQL

I’ve recently installed the Microsoft Small Business Accounting package. I have to say I’m enjoying it and think I’ll keep using it. Unfortunately, I didn’t like the Business Contact Manager for Outlook, as though the two would supposedly talk to one another, I didn’t think it integrated particularly well on either side.

In any case, the application makes use of MSDE for database storage. This is all fine and dandy but it doesn’t really help with backing up data or anything of the like. So I had to go digging through pages to find some decent Transact-SQL references. This one is to back up a database from the command line.

The first challenge I had was getting into the database. It appears that MSDE is configured during installation to use Windows authentication, with the user who is installing the app as the database owner. As such, you cannot specify the user name on the command line. That’s a real pain.

Instead, use the -E switch to establish a trusted connection – just make sure you are logged in as the user in question first! The database is called microsoftsmlbiz, and the computername is just the name of the computer on which you installed the software. Your command should look something like this:

  osql -S <computername>\microsoftsmlbiz -E

If all goes well, you will now have a prompt to enter commands. Once you’re there, you just need to feed the name of your database and your backup location to the processor:

  backup database <databasename> to disk = '<path>'
  go

Where the databasename is the name of the database and path is the path, including disk and filename, to where you’d like to save the backup. If you don’t want to go through the trouble of logging in, say if you’d like to schedule a job, then add the -Q parameter, with your command in quotes, to the end of the command:

  ... -Q "backup database <databasename> to disk = '<path>'"

The process will run your command (hopefully successfully) and exit when it’s done.